1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self) -> t.Any:
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self) -> t.List[t.Any]:
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    def is_type(self, *dtypes) -> bool:
 242        return self.type is not None and self.type.is_type(*dtypes)
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return t.cast(E, ancestor)
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes who's parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 492            if not type(node) is self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return self._to_s()
 500
 501    def sql(self, dialect: DialectType = None, **opts) -> str:
 502        """
 503        Returns SQL string representation of this tree.
 504
 505        Args:
 506            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 507            opts: other `sqlglot.generator.Generator` options.
 508
 509        Returns:
 510            The SQL string.
 511        """
 512        from sqlglot.dialects import Dialect
 513
 514        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 515
 516    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 517        indent = "" if not level else "\n"
 518        indent += "".join(["  "] * level)
 519        left = f"({self.key.upper()} "
 520
 521        args: t.Dict[str, t.Any] = {
 522            k: ", ".join(
 523                v._to_s(hide_missing=hide_missing, level=level + 1)
 524                if hasattr(v, "_to_s")
 525                else str(v)
 526                for v in ensure_list(vs)
 527                if v is not None
 528            )
 529            for k, vs in self.args.items()
 530        }
 531        args["comments"] = self.comments
 532        args["type"] = self.type
 533        args = {k: v for k, v in args.items() if v or not hide_missing}
 534
 535        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 536        right += ")"
 537
 538        return indent + left + right
 539
 540    def transform(self, fun, *args, copy=True, **kwargs):
 541        """
 542        Recursively visits all tree nodes (excluding already transformed ones)
 543        and applies the given transformation function to each node.
 544
 545        Args:
 546            fun (function): a function which takes a node as an argument and returns a
 547                new transformed node or the same node without modifications. If the function
 548                returns None, then the corresponding node will be removed from the syntax tree.
 549            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 550                modified in place.
 551
 552        Returns:
 553            The transformed tree.
 554        """
 555        node = self.copy() if copy else self
 556        new_node = fun(node, *args, **kwargs)
 557
 558        if new_node is None or not isinstance(new_node, Expression):
 559            return new_node
 560        if new_node is not node:
 561            new_node.parent = node.parent
 562            return new_node
 563
 564        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 565        return new_node
 566
 567    @t.overload
 568    def replace(self, expression: E) -> E:
 569        ...
 570
 571    @t.overload
 572    def replace(self, expression: None) -> None:
 573        ...
 574
 575    def replace(self, expression):
 576        """
 577        Swap out this expression with a new expression.
 578
 579        For example::
 580
 581            >>> tree = Select().select("x").from_("tbl")
 582            >>> tree.find(Column).replace(Column(this="y"))
 583            (COLUMN this: y)
 584            >>> tree.sql()
 585            'SELECT y FROM tbl'
 586
 587        Args:
 588            expression: new node
 589
 590        Returns:
 591            The new expression or expressions.
 592        """
 593        if not self.parent:
 594            return expression
 595
 596        parent = self.parent
 597        self.parent = None
 598
 599        replace_children(parent, lambda child: expression if child is self else child)
 600        return expression
 601
 602    def pop(self: E) -> E:
 603        """
 604        Remove this expression from its AST.
 605
 606        Returns:
 607            The popped expression.
 608        """
 609        self.replace(None)
 610        return self
 611
 612    def assert_is(self, type_: t.Type[E]) -> E:
 613        """
 614        Assert that this `Expression` is an instance of `type_`.
 615
 616        If it is NOT an instance of `type_`, this raises an assertion error.
 617        Otherwise, this returns this expression.
 618
 619        Examples:
 620            This is useful for type security in chained expressions:
 621
 622            >>> import sqlglot
 623            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 624            'SELECT x, z FROM y'
 625        """
 626        assert isinstance(self, type_)
 627        return self
 628
 629    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 630        """
 631        Checks if this expression is valid (e.g. all mandatory args are set).
 632
 633        Args:
 634            args: a sequence of values that were used to instantiate a Func expression. This is used
 635                to check that the provided arguments don't exceed the function argument limit.
 636
 637        Returns:
 638            A list of error messages for all possible errors that were found.
 639        """
 640        errors: t.List[str] = []
 641
 642        for k in self.args:
 643            if k not in self.arg_types:
 644                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 645        for k, mandatory in self.arg_types.items():
 646            v = self.args.get(k)
 647            if mandatory and (v is None or (isinstance(v, list) and not v)):
 648                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 649
 650        if (
 651            args
 652            and isinstance(self, Func)
 653            and len(args) > len(self.arg_types)
 654            and not self.is_var_len_args
 655        ):
 656            errors.append(
 657                f"The number of provided arguments ({len(args)}) is greater than "
 658                f"the maximum number of supported arguments ({len(self.arg_types)})"
 659            )
 660
 661        return errors
 662
 663    def dump(self):
 664        """
 665        Dump this Expression to a JSON-serializable dict.
 666        """
 667        from sqlglot.serde import dump
 668
 669        return dump(self)
 670
 671    @classmethod
 672    def load(cls, obj):
 673        """
 674        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 675        """
 676        from sqlglot.serde import load
 677
 678        return load(obj)
 679
 680    def and_(
 681        self,
 682        *expressions: t.Optional[ExpOrStr],
 683        dialect: DialectType = None,
 684        copy: bool = True,
 685        **opts,
 686    ) -> Condition:
 687        """
 688        AND this condition with one or multiple expressions.
 689
 690        Example:
 691            >>> condition("x=1").and_("y=1").sql()
 692            'x = 1 AND y = 1'
 693
 694        Args:
 695            *expressions: the SQL code strings to parse.
 696                If an `Expression` instance is passed, it will be used as-is.
 697            dialect: the dialect used to parse the input expression.
 698            copy: whether or not to copy the involved expressions (only applies to Expressions).
 699            opts: other options to use to parse the input expressions.
 700
 701        Returns:
 702            The new And condition.
 703        """
 704        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 705
 706    def or_(
 707        self,
 708        *expressions: t.Optional[ExpOrStr],
 709        dialect: DialectType = None,
 710        copy: bool = True,
 711        **opts,
 712    ) -> Condition:
 713        """
 714        OR this condition with one or multiple expressions.
 715
 716        Example:
 717            >>> condition("x=1").or_("y=1").sql()
 718            'x = 1 OR y = 1'
 719
 720        Args:
 721            *expressions: the SQL code strings to parse.
 722                If an `Expression` instance is passed, it will be used as-is.
 723            dialect: the dialect used to parse the input expression.
 724            copy: whether or not to copy the involved expressions (only applies to Expressions).
 725            opts: other options to use to parse the input expressions.
 726
 727        Returns:
 728            The new Or condition.
 729        """
 730        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 731
 732    def not_(self, copy: bool = True):
 733        """
 734        Wrap this condition with NOT.
 735
 736        Example:
 737            >>> condition("x=1").not_().sql()
 738            'NOT x = 1'
 739
 740        Args:
 741            copy: whether or not to copy this object.
 742
 743        Returns:
 744            The new Not instance.
 745        """
 746        return not_(self, copy=copy)
 747
 748    def as_(
 749        self,
 750        alias: str | Identifier,
 751        quoted: t.Optional[bool] = None,
 752        dialect: DialectType = None,
 753        copy: bool = True,
 754        **opts,
 755    ) -> Alias:
 756        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 757
 758    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 759        this = self.copy()
 760        other = convert(other, copy=True)
 761        if not isinstance(this, klass) and not isinstance(other, klass):
 762            this = _wrap(this, Binary)
 763            other = _wrap(other, Binary)
 764        if reverse:
 765            return klass(this=other, expression=this)
 766        return klass(this=this, expression=other)
 767
 768    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 769        return Bracket(
 770            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 771        )
 772
 773    def __iter__(self) -> t.Iterator:
 774        if "expressions" in self.arg_types:
 775            return iter(self.args.get("expressions") or [])
 776        # We define this because __getitem__ converts Expression into an iterable, which is
 777        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 778        # See: https://peps.python.org/pep-0234/
 779        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 780
 781    def isin(
 782        self,
 783        *expressions: t.Any,
 784        query: t.Optional[ExpOrStr] = None,
 785        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 786        copy: bool = True,
 787        **opts,
 788    ) -> In:
 789        return In(
 790            this=maybe_copy(self, copy),
 791            expressions=[convert(e, copy=copy) for e in expressions],
 792            query=maybe_parse(query, copy=copy, **opts) if query else None,
 793            unnest=Unnest(
 794                expressions=[
 795                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 796                ]
 797            )
 798            if unnest
 799            else None,
 800        )
 801
 802    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 803        return Between(
 804            this=maybe_copy(self, copy),
 805            low=convert(low, copy=copy, **opts),
 806            high=convert(high, copy=copy, **opts),
 807        )
 808
 809    def is_(self, other: ExpOrStr) -> Is:
 810        return self._binop(Is, other)
 811
 812    def like(self, other: ExpOrStr) -> Like:
 813        return self._binop(Like, other)
 814
 815    def ilike(self, other: ExpOrStr) -> ILike:
 816        return self._binop(ILike, other)
 817
 818    def eq(self, other: t.Any) -> EQ:
 819        return self._binop(EQ, other)
 820
 821    def neq(self, other: t.Any) -> NEQ:
 822        return self._binop(NEQ, other)
 823
 824    def rlike(self, other: ExpOrStr) -> RegexpLike:
 825        return self._binop(RegexpLike, other)
 826
 827    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 828        div = self._binop(Div, other)
 829        div.args["typed"] = typed
 830        div.args["safe"] = safe
 831        return div
 832
 833    def __lt__(self, other: t.Any) -> LT:
 834        return self._binop(LT, other)
 835
 836    def __le__(self, other: t.Any) -> LTE:
 837        return self._binop(LTE, other)
 838
 839    def __gt__(self, other: t.Any) -> GT:
 840        return self._binop(GT, other)
 841
 842    def __ge__(self, other: t.Any) -> GTE:
 843        return self._binop(GTE, other)
 844
 845    def __add__(self, other: t.Any) -> Add:
 846        return self._binop(Add, other)
 847
 848    def __radd__(self, other: t.Any) -> Add:
 849        return self._binop(Add, other, reverse=True)
 850
 851    def __sub__(self, other: t.Any) -> Sub:
 852        return self._binop(Sub, other)
 853
 854    def __rsub__(self, other: t.Any) -> Sub:
 855        return self._binop(Sub, other, reverse=True)
 856
 857    def __mul__(self, other: t.Any) -> Mul:
 858        return self._binop(Mul, other)
 859
 860    def __rmul__(self, other: t.Any) -> Mul:
 861        return self._binop(Mul, other, reverse=True)
 862
 863    def __truediv__(self, other: t.Any) -> Div:
 864        return self._binop(Div, other)
 865
 866    def __rtruediv__(self, other: t.Any) -> Div:
 867        return self._binop(Div, other, reverse=True)
 868
 869    def __floordiv__(self, other: t.Any) -> IntDiv:
 870        return self._binop(IntDiv, other)
 871
 872    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 873        return self._binop(IntDiv, other, reverse=True)
 874
 875    def __mod__(self, other: t.Any) -> Mod:
 876        return self._binop(Mod, other)
 877
 878    def __rmod__(self, other: t.Any) -> Mod:
 879        return self._binop(Mod, other, reverse=True)
 880
 881    def __pow__(self, other: t.Any) -> Pow:
 882        return self._binop(Pow, other)
 883
 884    def __rpow__(self, other: t.Any) -> Pow:
 885        return self._binop(Pow, other, reverse=True)
 886
 887    def __and__(self, other: t.Any) -> And:
 888        return self._binop(And, other)
 889
 890    def __rand__(self, other: t.Any) -> And:
 891        return self._binop(And, other, reverse=True)
 892
 893    def __or__(self, other: t.Any) -> Or:
 894        return self._binop(Or, other)
 895
 896    def __ror__(self, other: t.Any) -> Or:
 897        return self._binop(Or, other, reverse=True)
 898
 899    def __neg__(self) -> Neg:
 900        return Neg(this=_wrap(self.copy(), Binary))
 901
 902    def __invert__(self) -> Not:
 903        return not_(self.copy())
 904
 905
 906IntoType = t.Union[
 907    str,
 908    t.Type[Expression],
 909    t.Collection[t.Union[str, t.Type[Expression]]],
 910]
 911ExpOrStr = t.Union[str, Expression]
 912
 913
 914class Condition(Expression):
 915    """Logical conditions like x AND y, or simply x"""
 916
 917
 918class Predicate(Condition):
 919    """Relationships like x = y, x > 1, x >= y."""
 920
 921
 922class DerivedTable(Expression):
 923    @property
 924    def selects(self) -> t.List[Expression]:
 925        return self.this.selects if isinstance(self.this, Subqueryable) else []
 926
 927    @property
 928    def named_selects(self) -> t.List[str]:
 929        return [select.output_name for select in self.selects]
 930
 931
 932class Unionable(Expression):
 933    def union(
 934        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 935    ) -> Unionable:
 936        """
 937        Builds a UNION expression.
 938
 939        Example:
 940            >>> import sqlglot
 941            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 942            'SELECT * FROM foo UNION SELECT * FROM bla'
 943
 944        Args:
 945            expression: the SQL code string.
 946                If an `Expression` instance is passed, it will be used as-is.
 947            distinct: set the DISTINCT flag if and only if this is true.
 948            dialect: the dialect used to parse the input expression.
 949            opts: other options to use to parse the input expressions.
 950
 951        Returns:
 952            The new Union expression.
 953        """
 954        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 955
 956    def intersect(
 957        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 958    ) -> Unionable:
 959        """
 960        Builds an INTERSECT expression.
 961
 962        Example:
 963            >>> import sqlglot
 964            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 965            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 966
 967        Args:
 968            expression: the SQL code string.
 969                If an `Expression` instance is passed, it will be used as-is.
 970            distinct: set the DISTINCT flag if and only if this is true.
 971            dialect: the dialect used to parse the input expression.
 972            opts: other options to use to parse the input expressions.
 973
 974        Returns:
 975            The new Intersect expression.
 976        """
 977        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 978
 979    def except_(
 980        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 981    ) -> Unionable:
 982        """
 983        Builds an EXCEPT expression.
 984
 985        Example:
 986            >>> import sqlglot
 987            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 988            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 989
 990        Args:
 991            expression: the SQL code string.
 992                If an `Expression` instance is passed, it will be used as-is.
 993            distinct: set the DISTINCT flag if and only if this is true.
 994            dialect: the dialect used to parse the input expression.
 995            opts: other options to use to parse the input expressions.
 996
 997        Returns:
 998            The new Except expression.
 999        """
1000        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1001
1002
1003class UDTF(DerivedTable, Unionable):
1004    @property
1005    def selects(self) -> t.List[Expression]:
1006        alias = self.args.get("alias")
1007        return alias.columns if alias else []
1008
1009
1010class Cache(Expression):
1011    arg_types = {
1012        "with": False,
1013        "this": True,
1014        "lazy": False,
1015        "options": False,
1016        "expression": False,
1017    }
1018
1019
1020class Uncache(Expression):
1021    arg_types = {"this": True, "exists": False}
1022
1023
1024class DDL(Expression):
1025    @property
1026    def ctes(self):
1027        with_ = self.args.get("with")
1028        if not with_:
1029            return []
1030        return with_.expressions
1031
1032    @property
1033    def named_selects(self) -> t.List[str]:
1034        if isinstance(self.expression, Subqueryable):
1035            return self.expression.named_selects
1036        return []
1037
1038    @property
1039    def selects(self) -> t.List[Expression]:
1040        if isinstance(self.expression, Subqueryable):
1041            return self.expression.selects
1042        return []
1043
1044
1045class Create(DDL):
1046    arg_types = {
1047        "with": False,
1048        "this": True,
1049        "kind": True,
1050        "expression": False,
1051        "exists": False,
1052        "properties": False,
1053        "replace": False,
1054        "unique": False,
1055        "indexes": False,
1056        "no_schema_binding": False,
1057        "begin": False,
1058        "end": False,
1059        "clone": False,
1060    }
1061
1062
1063# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1064# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1065# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1066class Clone(Expression):
1067    arg_types = {
1068        "this": True,
1069        "when": False,
1070        "kind": False,
1071        "shallow": False,
1072        "expression": False,
1073        "copy": False,
1074    }
1075
1076
1077class Describe(Expression):
1078    arg_types = {"this": True, "kind": False, "expressions": False}
1079
1080
1081class Kill(Expression):
1082    arg_types = {"this": True, "kind": False}
1083
1084
1085class Pragma(Expression):
1086    pass
1087
1088
1089class Set(Expression):
1090    arg_types = {"expressions": False, "unset": False, "tag": False}
1091
1092
1093class SetItem(Expression):
1094    arg_types = {
1095        "this": False,
1096        "expressions": False,
1097        "kind": False,
1098        "collate": False,  # MySQL SET NAMES statement
1099        "global": False,
1100    }
1101
1102
1103class Show(Expression):
1104    arg_types = {
1105        "this": True,
1106        "target": False,
1107        "offset": False,
1108        "limit": False,
1109        "like": False,
1110        "where": False,
1111        "db": False,
1112        "scope": False,
1113        "scope_kind": False,
1114        "full": False,
1115        "mutex": False,
1116        "query": False,
1117        "channel": False,
1118        "global": False,
1119        "log": False,
1120        "position": False,
1121        "types": False,
1122    }
1123
1124
1125class UserDefinedFunction(Expression):
1126    arg_types = {"this": True, "expressions": False, "wrapped": False}
1127
1128
1129class CharacterSet(Expression):
1130    arg_types = {"this": True, "default": False}
1131
1132
1133class With(Expression):
1134    arg_types = {"expressions": True, "recursive": False}
1135
1136    @property
1137    def recursive(self) -> bool:
1138        return bool(self.args.get("recursive"))
1139
1140
1141class WithinGroup(Expression):
1142    arg_types = {"this": True, "expression": False}
1143
1144
1145class CTE(DerivedTable):
1146    arg_types = {"this": True, "alias": True}
1147
1148
1149class TableAlias(Expression):
1150    arg_types = {"this": False, "columns": False}
1151
1152    @property
1153    def columns(self):
1154        return self.args.get("columns") or []
1155
1156
1157class BitString(Condition):
1158    pass
1159
1160
1161class HexString(Condition):
1162    pass
1163
1164
1165class ByteString(Condition):
1166    pass
1167
1168
1169class RawString(Condition):
1170    pass
1171
1172
1173class Column(Condition):
1174    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1175
1176    @property
1177    def table(self) -> str:
1178        return self.text("table")
1179
1180    @property
1181    def db(self) -> str:
1182        return self.text("db")
1183
1184    @property
1185    def catalog(self) -> str:
1186        return self.text("catalog")
1187
1188    @property
1189    def output_name(self) -> str:
1190        return self.name
1191
1192    @property
1193    def parts(self) -> t.List[Identifier]:
1194        """Return the parts of a column in order catalog, db, table, name."""
1195        return [
1196            t.cast(Identifier, self.args[part])
1197            for part in ("catalog", "db", "table", "this")
1198            if self.args.get(part)
1199        ]
1200
1201    def to_dot(self) -> Dot | Identifier:
1202        """Converts the column into a dot expression."""
1203        parts = self.parts
1204        parent = self.parent
1205
1206        while parent:
1207            if isinstance(parent, Dot):
1208                parts.append(parent.expression)
1209            parent = parent.parent
1210
1211        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1212
1213
1214class ColumnPosition(Expression):
1215    arg_types = {"this": False, "position": True}
1216
1217
1218class ColumnDef(Expression):
1219    arg_types = {
1220        "this": True,
1221        "kind": False,
1222        "constraints": False,
1223        "exists": False,
1224        "position": False,
1225    }
1226
1227    @property
1228    def constraints(self) -> t.List[ColumnConstraint]:
1229        return self.args.get("constraints") or []
1230
1231
1232class AlterColumn(Expression):
1233    arg_types = {
1234        "this": True,
1235        "dtype": False,
1236        "collate": False,
1237        "using": False,
1238        "default": False,
1239        "drop": False,
1240    }
1241
1242
1243class RenameTable(Expression):
1244    pass
1245
1246
1247class SwapTable(Expression):
1248    pass
1249
1250
1251class Comment(Expression):
1252    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1253
1254
1255class Comprehension(Expression):
1256    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1257
1258
1259# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1260class MergeTreeTTLAction(Expression):
1261    arg_types = {
1262        "this": True,
1263        "delete": False,
1264        "recompress": False,
1265        "to_disk": False,
1266        "to_volume": False,
1267    }
1268
1269
1270# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1271class MergeTreeTTL(Expression):
1272    arg_types = {
1273        "expressions": True,
1274        "where": False,
1275        "group": False,
1276        "aggregates": False,
1277    }
1278
1279
1280# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1281class IndexConstraintOption(Expression):
1282    arg_types = {
1283        "key_block_size": False,
1284        "using": False,
1285        "parser": False,
1286        "comment": False,
1287        "visible": False,
1288        "engine_attr": False,
1289        "secondary_engine_attr": False,
1290    }
1291
1292
1293class ColumnConstraint(Expression):
1294    arg_types = {"this": False, "kind": True}
1295
1296    @property
1297    def kind(self) -> ColumnConstraintKind:
1298        return self.args["kind"]
1299
1300
1301class ColumnConstraintKind(Expression):
1302    pass
1303
1304
1305class AutoIncrementColumnConstraint(ColumnConstraintKind):
1306    pass
1307
1308
1309class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1310    arg_types = {"this": True, "expression": True}
1311
1312
1313class CaseSpecificColumnConstraint(ColumnConstraintKind):
1314    arg_types = {"not_": True}
1315
1316
1317class CharacterSetColumnConstraint(ColumnConstraintKind):
1318    arg_types = {"this": True}
1319
1320
1321class CheckColumnConstraint(ColumnConstraintKind):
1322    pass
1323
1324
1325class ClusteredColumnConstraint(ColumnConstraintKind):
1326    pass
1327
1328
1329class CollateColumnConstraint(ColumnConstraintKind):
1330    pass
1331
1332
1333class CommentColumnConstraint(ColumnConstraintKind):
1334    pass
1335
1336
1337class CompressColumnConstraint(ColumnConstraintKind):
1338    pass
1339
1340
1341class DateFormatColumnConstraint(ColumnConstraintKind):
1342    arg_types = {"this": True}
1343
1344
1345class DefaultColumnConstraint(ColumnConstraintKind):
1346    pass
1347
1348
1349class EncodeColumnConstraint(ColumnConstraintKind):
1350    pass
1351
1352
1353class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1354    # this: True -> ALWAYS, this: False -> BY DEFAULT
1355    arg_types = {
1356        "this": False,
1357        "expression": False,
1358        "on_null": False,
1359        "start": False,
1360        "increment": False,
1361        "minvalue": False,
1362        "maxvalue": False,
1363        "cycle": False,
1364    }
1365
1366
1367class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1368    arg_types = {"start": True, "hidden": False}
1369
1370
1371# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1372class IndexColumnConstraint(ColumnConstraintKind):
1373    arg_types = {
1374        "this": False,
1375        "schema": True,
1376        "kind": False,
1377        "index_type": False,
1378        "options": False,
1379    }
1380
1381
1382class InlineLengthColumnConstraint(ColumnConstraintKind):
1383    pass
1384
1385
1386class NonClusteredColumnConstraint(ColumnConstraintKind):
1387    pass
1388
1389
1390class NotForReplicationColumnConstraint(ColumnConstraintKind):
1391    arg_types = {}
1392
1393
1394class NotNullColumnConstraint(ColumnConstraintKind):
1395    arg_types = {"allow_null": False}
1396
1397
1398# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1399class OnUpdateColumnConstraint(ColumnConstraintKind):
1400    pass
1401
1402
1403class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1404    arg_types = {"desc": False}
1405
1406
1407class TitleColumnConstraint(ColumnConstraintKind):
1408    pass
1409
1410
1411class UniqueColumnConstraint(ColumnConstraintKind):
1412    arg_types = {"this": False, "index_type": False}
1413
1414
1415class UppercaseColumnConstraint(ColumnConstraintKind):
1416    arg_types: t.Dict[str, t.Any] = {}
1417
1418
1419class PathColumnConstraint(ColumnConstraintKind):
1420    pass
1421
1422
1423# computed column expression
1424# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1425class ComputedColumnConstraint(ColumnConstraintKind):
1426    arg_types = {"this": True, "persisted": False, "not_null": False}
1427
1428
1429class Constraint(Expression):
1430    arg_types = {"this": True, "expressions": True}
1431
1432
1433class Delete(Expression):
1434    arg_types = {
1435        "with": False,
1436        "this": False,
1437        "using": False,
1438        "where": False,
1439        "returning": False,
1440        "limit": False,
1441        "tables": False,  # Multiple-Table Syntax (MySQL)
1442    }
1443
1444    def delete(
1445        self,
1446        table: ExpOrStr,
1447        dialect: DialectType = None,
1448        copy: bool = True,
1449        **opts,
1450    ) -> Delete:
1451        """
1452        Create a DELETE expression or replace the table on an existing DELETE expression.
1453
1454        Example:
1455            >>> delete("tbl").sql()
1456            'DELETE FROM tbl'
1457
1458        Args:
1459            table: the table from which to delete.
1460            dialect: the dialect used to parse the input expression.
1461            copy: if `False`, modify this expression instance in-place.
1462            opts: other options to use to parse the input expressions.
1463
1464        Returns:
1465            Delete: the modified expression.
1466        """
1467        return _apply_builder(
1468            expression=table,
1469            instance=self,
1470            arg="this",
1471            dialect=dialect,
1472            into=Table,
1473            copy=copy,
1474            **opts,
1475        )
1476
1477    def where(
1478        self,
1479        *expressions: t.Optional[ExpOrStr],
1480        append: bool = True,
1481        dialect: DialectType = None,
1482        copy: bool = True,
1483        **opts,
1484    ) -> Delete:
1485        """
1486        Append to or set the WHERE expressions.
1487
1488        Example:
1489            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1490            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1491
1492        Args:
1493            *expressions: the SQL code strings to parse.
1494                If an `Expression` instance is passed, it will be used as-is.
1495                Multiple expressions are combined with an AND operator.
1496            append: if `True`, AND the new expressions to any existing expression.
1497                Otherwise, this resets the expression.
1498            dialect: the dialect used to parse the input expressions.
1499            copy: if `False`, modify this expression instance in-place.
1500            opts: other options to use to parse the input expressions.
1501
1502        Returns:
1503            Delete: the modified expression.
1504        """
1505        return _apply_conjunction_builder(
1506            *expressions,
1507            instance=self,
1508            arg="where",
1509            append=append,
1510            into=Where,
1511            dialect=dialect,
1512            copy=copy,
1513            **opts,
1514        )
1515
1516    def returning(
1517        self,
1518        expression: ExpOrStr,
1519        dialect: DialectType = None,
1520        copy: bool = True,
1521        **opts,
1522    ) -> Delete:
1523        """
1524        Set the RETURNING expression. Not supported by all dialects.
1525
1526        Example:
1527            >>> delete("tbl").returning("*", dialect="postgres").sql()
1528            'DELETE FROM tbl RETURNING *'
1529
1530        Args:
1531            expression: the SQL code strings to parse.
1532                If an `Expression` instance is passed, it will be used as-is.
1533            dialect: the dialect used to parse the input expressions.
1534            copy: if `False`, modify this expression instance in-place.
1535            opts: other options to use to parse the input expressions.
1536
1537        Returns:
1538            Delete: the modified expression.
1539        """
1540        return _apply_builder(
1541            expression=expression,
1542            instance=self,
1543            arg="returning",
1544            prefix="RETURNING",
1545            dialect=dialect,
1546            copy=copy,
1547            into=Returning,
1548            **opts,
1549        )
1550
1551
1552class Drop(Expression):
1553    arg_types = {
1554        "this": False,
1555        "kind": False,
1556        "exists": False,
1557        "temporary": False,
1558        "materialized": False,
1559        "cascade": False,
1560        "constraints": False,
1561        "purge": False,
1562    }
1563
1564
1565class Filter(Expression):
1566    arg_types = {"this": True, "expression": True}
1567
1568
1569class Check(Expression):
1570    pass
1571
1572
1573# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1574class Connect(Expression):
1575    arg_types = {"start": False, "connect": True}
1576
1577
1578class Prior(Expression):
1579    pass
1580
1581
1582class Directory(Expression):
1583    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1584    arg_types = {"this": True, "local": False, "row_format": False}
1585
1586
1587class ForeignKey(Expression):
1588    arg_types = {
1589        "expressions": True,
1590        "reference": False,
1591        "delete": False,
1592        "update": False,
1593    }
1594
1595
1596class ColumnPrefix(Expression):
1597    arg_types = {"this": True, "expression": True}
1598
1599
1600class PrimaryKey(Expression):
1601    arg_types = {"expressions": True, "options": False}
1602
1603
1604# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1605# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1606class Into(Expression):
1607    arg_types = {"this": True, "temporary": False, "unlogged": False}
1608
1609
1610class From(Expression):
1611    @property
1612    def name(self) -> str:
1613        return self.this.name
1614
1615    @property
1616    def alias_or_name(self) -> str:
1617        return self.this.alias_or_name
1618
1619
1620class Having(Expression):
1621    pass
1622
1623
1624class Hint(Expression):
1625    arg_types = {"expressions": True}
1626
1627
1628class JoinHint(Expression):
1629    arg_types = {"this": True, "expressions": True}
1630
1631
1632class Identifier(Expression):
1633    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1634
1635    @property
1636    def quoted(self) -> bool:
1637        return bool(self.args.get("quoted"))
1638
1639    @property
1640    def hashable_args(self) -> t.Any:
1641        return (self.this, self.quoted)
1642
1643    @property
1644    def output_name(self) -> str:
1645        return self.name
1646
1647
1648# https://www.postgresql.org/docs/current/indexes-opclass.html
1649class Opclass(Expression):
1650    arg_types = {"this": True, "expression": True}
1651
1652
1653class Index(Expression):
1654    arg_types = {
1655        "this": False,
1656        "table": False,
1657        "using": False,
1658        "where": False,
1659        "columns": False,
1660        "unique": False,
1661        "primary": False,
1662        "amp": False,  # teradata
1663        "partition_by": False,  # teradata
1664        "where": False,  # postgres partial indexes
1665    }
1666
1667
1668class Insert(DDL):
1669    arg_types = {
1670        "with": False,
1671        "this": True,
1672        "expression": False,
1673        "conflict": False,
1674        "returning": False,
1675        "overwrite": False,
1676        "exists": False,
1677        "partition": False,
1678        "alternative": False,
1679        "where": False,
1680        "ignore": False,
1681        "by_name": False,
1682    }
1683
1684    def with_(
1685        self,
1686        alias: ExpOrStr,
1687        as_: ExpOrStr,
1688        recursive: t.Optional[bool] = None,
1689        append: bool = True,
1690        dialect: DialectType = None,
1691        copy: bool = True,
1692        **opts,
1693    ) -> Insert:
1694        """
1695        Append to or set the common table expressions.
1696
1697        Example:
1698            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1699            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1700
1701        Args:
1702            alias: the SQL code string to parse as the table name.
1703                If an `Expression` instance is passed, this is used as-is.
1704            as_: the SQL code string to parse as the table expression.
1705                If an `Expression` instance is passed, it will be used as-is.
1706            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1707            append: if `True`, add to any existing expressions.
1708                Otherwise, this resets the expressions.
1709            dialect: the dialect used to parse the input expression.
1710            copy: if `False`, modify this expression instance in-place.
1711            opts: other options to use to parse the input expressions.
1712
1713        Returns:
1714            The modified expression.
1715        """
1716        return _apply_cte_builder(
1717            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1718        )
1719
1720
1721class OnConflict(Expression):
1722    arg_types = {
1723        "duplicate": False,
1724        "expressions": False,
1725        "nothing": False,
1726        "key": False,
1727        "constraint": False,
1728    }
1729
1730
1731class Returning(Expression):
1732    arg_types = {"expressions": True, "into": False}
1733
1734
1735# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1736class Introducer(Expression):
1737    arg_types = {"this": True, "expression": True}
1738
1739
1740# national char, like n'utf8'
1741class National(Expression):
1742    pass
1743
1744
1745class LoadData(Expression):
1746    arg_types = {
1747        "this": True,
1748        "local": False,
1749        "overwrite": False,
1750        "inpath": True,
1751        "partition": False,
1752        "input_format": False,
1753        "serde": False,
1754    }
1755
1756
1757class Partition(Expression):
1758    arg_types = {"expressions": True}
1759
1760
1761class Fetch(Expression):
1762    arg_types = {
1763        "direction": False,
1764        "count": False,
1765        "percent": False,
1766        "with_ties": False,
1767    }
1768
1769
1770class Group(Expression):
1771    arg_types = {
1772        "expressions": False,
1773        "grouping_sets": False,
1774        "cube": False,
1775        "rollup": False,
1776        "totals": False,
1777        "all": False,
1778    }
1779
1780
1781class Lambda(Expression):
1782    arg_types = {"this": True, "expressions": True}
1783
1784
1785class Limit(Expression):
1786    arg_types = {"this": False, "expression": True, "offset": False}
1787
1788
1789class Literal(Condition):
1790    arg_types = {"this": True, "is_string": True}
1791
1792    @property
1793    def hashable_args(self) -> t.Any:
1794        return (self.this, self.args.get("is_string"))
1795
1796    @classmethod
1797    def number(cls, number) -> Literal:
1798        return cls(this=str(number), is_string=False)
1799
1800    @classmethod
1801    def string(cls, string) -> Literal:
1802        return cls(this=str(string), is_string=True)
1803
1804    @property
1805    def output_name(self) -> str:
1806        return self.name
1807
1808
1809class Join(Expression):
1810    arg_types = {
1811        "this": True,
1812        "on": False,
1813        "side": False,
1814        "kind": False,
1815        "using": False,
1816        "method": False,
1817        "global": False,
1818        "hint": False,
1819    }
1820
1821    @property
1822    def method(self) -> str:
1823        return self.text("method").upper()
1824
1825    @property
1826    def kind(self) -> str:
1827        return self.text("kind").upper()
1828
1829    @property
1830    def side(self) -> str:
1831        return self.text("side").upper()
1832
1833    @property
1834    def hint(self) -> str:
1835        return self.text("hint").upper()
1836
1837    @property
1838    def alias_or_name(self) -> str:
1839        return self.this.alias_or_name
1840
1841    def on(
1842        self,
1843        *expressions: t.Optional[ExpOrStr],
1844        append: bool = True,
1845        dialect: DialectType = None,
1846        copy: bool = True,
1847        **opts,
1848    ) -> Join:
1849        """
1850        Append to or set the ON expressions.
1851
1852        Example:
1853            >>> import sqlglot
1854            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1855            'JOIN x ON y = 1'
1856
1857        Args:
1858            *expressions: the SQL code strings to parse.
1859                If an `Expression` instance is passed, it will be used as-is.
1860                Multiple expressions are combined with an AND operator.
1861            append: if `True`, AND the new expressions to any existing expression.
1862                Otherwise, this resets the expression.
1863            dialect: the dialect used to parse the input expressions.
1864            copy: if `False`, modify this expression instance in-place.
1865            opts: other options to use to parse the input expressions.
1866
1867        Returns:
1868            The modified Join expression.
1869        """
1870        join = _apply_conjunction_builder(
1871            *expressions,
1872            instance=self,
1873            arg="on",
1874            append=append,
1875            dialect=dialect,
1876            copy=copy,
1877            **opts,
1878        )
1879
1880        if join.kind == "CROSS":
1881            join.set("kind", None)
1882
1883        return join
1884
1885    def using(
1886        self,
1887        *expressions: t.Optional[ExpOrStr],
1888        append: bool = True,
1889        dialect: DialectType = None,
1890        copy: bool = True,
1891        **opts,
1892    ) -> Join:
1893        """
1894        Append to or set the USING expressions.
1895
1896        Example:
1897            >>> import sqlglot
1898            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1899            'JOIN x USING (foo, bla)'
1900
1901        Args:
1902            *expressions: the SQL code strings to parse.
1903                If an `Expression` instance is passed, it will be used as-is.
1904            append: if `True`, concatenate the new expressions to the existing "using" list.
1905                Otherwise, this resets the expression.
1906            dialect: the dialect used to parse the input expressions.
1907            copy: if `False`, modify this expression instance in-place.
1908            opts: other options to use to parse the input expressions.
1909
1910        Returns:
1911            The modified Join expression.
1912        """
1913        join = _apply_list_builder(
1914            *expressions,
1915            instance=self,
1916            arg="using",
1917            append=append,
1918            dialect=dialect,
1919            copy=copy,
1920            **opts,
1921        )
1922
1923        if join.kind == "CROSS":
1924            join.set("kind", None)
1925
1926        return join
1927
1928
1929class Lateral(UDTF):
1930    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1931
1932
1933class MatchRecognize(Expression):
1934    arg_types = {
1935        "partition_by": False,
1936        "order": False,
1937        "measures": False,
1938        "rows": False,
1939        "after": False,
1940        "pattern": False,
1941        "define": False,
1942        "alias": False,
1943    }
1944
1945
1946# Clickhouse FROM FINAL modifier
1947# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1948class Final(Expression):
1949    pass
1950
1951
1952class Offset(Expression):
1953    arg_types = {"this": False, "expression": True}
1954
1955
1956class Order(Expression):
1957    arg_types = {"this": False, "expressions": True}
1958
1959
1960# hive specific sorts
1961# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1962class Cluster(Order):
1963    pass
1964
1965
1966class Distribute(Order):
1967    pass
1968
1969
1970class Sort(Order):
1971    pass
1972
1973
1974class Ordered(Expression):
1975    arg_types = {"this": True, "desc": False, "nulls_first": True}
1976
1977
1978class Property(Expression):
1979    arg_types = {"this": True, "value": True}
1980
1981
1982class AlgorithmProperty(Property):
1983    arg_types = {"this": True}
1984
1985
1986class AutoIncrementProperty(Property):
1987    arg_types = {"this": True}
1988
1989
1990class BlockCompressionProperty(Property):
1991    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1992
1993
1994class CharacterSetProperty(Property):
1995    arg_types = {"this": True, "default": True}
1996
1997
1998class ChecksumProperty(Property):
1999    arg_types = {"on": False, "default": False}
2000
2001
2002class CollateProperty(Property):
2003    arg_types = {"this": True, "default": False}
2004
2005
2006class CopyGrantsProperty(Property):
2007    arg_types = {}
2008
2009
2010class DataBlocksizeProperty(Property):
2011    arg_types = {
2012        "size": False,
2013        "units": False,
2014        "minimum": False,
2015        "maximum": False,
2016        "default": False,
2017    }
2018
2019
2020class DefinerProperty(Property):
2021    arg_types = {"this": True}
2022
2023
2024class DistKeyProperty(Property):
2025    arg_types = {"this": True}
2026
2027
2028class DistStyleProperty(Property):
2029    arg_types = {"this": True}
2030
2031
2032class EngineProperty(Property):
2033    arg_types = {"this": True}
2034
2035
2036class HeapProperty(Property):
2037    arg_types = {}
2038
2039
2040class ToTableProperty(Property):
2041    arg_types = {"this": True}
2042
2043
2044class ExecuteAsProperty(Property):
2045    arg_types = {"this": True}
2046
2047
2048class ExternalProperty(Property):
2049    arg_types = {"this": False}
2050
2051
2052class FallbackProperty(Property):
2053    arg_types = {"no": True, "protection": False}
2054
2055
2056class FileFormatProperty(Property):
2057    arg_types = {"this": True}
2058
2059
2060class FreespaceProperty(Property):
2061    arg_types = {"this": True, "percent": False}
2062
2063
2064class InputModelProperty(Property):
2065    arg_types = {"this": True}
2066
2067
2068class OutputModelProperty(Property):
2069    arg_types = {"this": True}
2070
2071
2072class IsolatedLoadingProperty(Property):
2073    arg_types = {
2074        "no": True,
2075        "concurrent": True,
2076        "for_all": True,
2077        "for_insert": True,
2078        "for_none": True,
2079    }
2080
2081
2082class JournalProperty(Property):
2083    arg_types = {
2084        "no": False,
2085        "dual": False,
2086        "before": False,
2087        "local": False,
2088        "after": False,
2089    }
2090
2091
2092class LanguageProperty(Property):
2093    arg_types = {"this": True}
2094
2095
2096# spark ddl
2097class ClusteredByProperty(Property):
2098    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2099
2100
2101class DictProperty(Property):
2102    arg_types = {"this": True, "kind": True, "settings": False}
2103
2104
2105class DictSubProperty(Property):
2106    pass
2107
2108
2109class DictRange(Property):
2110    arg_types = {"this": True, "min": True, "max": True}
2111
2112
2113# Clickhouse CREATE ... ON CLUSTER modifier
2114# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2115class OnCluster(Property):
2116    arg_types = {"this": True}
2117
2118
2119class LikeProperty(Property):
2120    arg_types = {"this": True, "expressions": False}
2121
2122
2123class LocationProperty(Property):
2124    arg_types = {"this": True}
2125
2126
2127class LockingProperty(Property):
2128    arg_types = {
2129        "this": False,
2130        "kind": True,
2131        "for_or_in": False,
2132        "lock_type": True,
2133        "override": False,
2134    }
2135
2136
2137class LogProperty(Property):
2138    arg_types = {"no": True}
2139
2140
2141class MaterializedProperty(Property):
2142    arg_types = {"this": False}
2143
2144
2145class MergeBlockRatioProperty(Property):
2146    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2147
2148
2149class NoPrimaryIndexProperty(Property):
2150    arg_types = {}
2151
2152
2153class OnProperty(Property):
2154    arg_types = {"this": True}
2155
2156
2157class OnCommitProperty(Property):
2158    arg_types = {"delete": False}
2159
2160
2161class PartitionedByProperty(Property):
2162    arg_types = {"this": True}
2163
2164
2165# https://www.postgresql.org/docs/current/sql-createtable.html
2166class PartitionBoundSpec(Expression):
2167    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2168    arg_types = {
2169        "this": False,
2170        "expression": False,
2171        "from_expressions": False,
2172        "to_expressions": False,
2173    }
2174
2175
2176class PartitionedOfProperty(Property):
2177    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2178    arg_types = {"this": True, "expression": True}
2179
2180
2181class RemoteWithConnectionModelProperty(Property):
2182    arg_types = {"this": True}
2183
2184
2185class ReturnsProperty(Property):
2186    arg_types = {"this": True, "is_table": False, "table": False}
2187
2188
2189class RowFormatProperty(Property):
2190    arg_types = {"this": True}
2191
2192
2193class RowFormatDelimitedProperty(Property):
2194    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2195    arg_types = {
2196        "fields": False,
2197        "escaped": False,
2198        "collection_items": False,
2199        "map_keys": False,
2200        "lines": False,
2201        "null": False,
2202        "serde": False,
2203    }
2204
2205
2206class RowFormatSerdeProperty(Property):
2207    arg_types = {"this": True, "serde_properties": False}
2208
2209
2210# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2211class QueryTransform(Expression):
2212    arg_types = {
2213        "expressions": True,
2214        "command_script": True,
2215        "schema": False,
2216        "row_format_before": False,
2217        "record_writer": False,
2218        "row_format_after": False,
2219        "record_reader": False,
2220    }
2221
2222
2223class SampleProperty(Property):
2224    arg_types = {"this": True}
2225
2226
2227class SchemaCommentProperty(Property):
2228    arg_types = {"this": True}
2229
2230
2231class SerdeProperties(Property):
2232    arg_types = {"expressions": True}
2233
2234
2235class SetProperty(Property):
2236    arg_types = {"multi": True}
2237
2238
2239class SettingsProperty(Property):
2240    arg_types = {"expressions": True}
2241
2242
2243class SortKeyProperty(Property):
2244    arg_types = {"this": True, "compound": False}
2245
2246
2247class SqlSecurityProperty(Property):
2248    arg_types = {"definer": True}
2249
2250
2251class StabilityProperty(Property):
2252    arg_types = {"this": True}
2253
2254
2255class TemporaryProperty(Property):
2256    arg_types = {}
2257
2258
2259class TransformModelProperty(Property):
2260    arg_types = {"expressions": True}
2261
2262
2263class TransientProperty(Property):
2264    arg_types = {"this": False}
2265
2266
2267class VolatileProperty(Property):
2268    arg_types = {"this": False}
2269
2270
2271class WithDataProperty(Property):
2272    arg_types = {"no": True, "statistics": False}
2273
2274
2275class WithJournalTableProperty(Property):
2276    arg_types = {"this": True}
2277
2278
2279class WithSystemVersioningProperty(Property):
2280    # this -> history table name, expression -> data consistency check
2281    arg_types = {"this": False, "expression": False}
2282
2283
2284class Properties(Expression):
2285    arg_types = {"expressions": True}
2286
2287    NAME_TO_PROPERTY = {
2288        "ALGORITHM": AlgorithmProperty,
2289        "AUTO_INCREMENT": AutoIncrementProperty,
2290        "CHARACTER SET": CharacterSetProperty,
2291        "CLUSTERED_BY": ClusteredByProperty,
2292        "COLLATE": CollateProperty,
2293        "COMMENT": SchemaCommentProperty,
2294        "DEFINER": DefinerProperty,
2295        "DISTKEY": DistKeyProperty,
2296        "DISTSTYLE": DistStyleProperty,
2297        "ENGINE": EngineProperty,
2298        "EXECUTE AS": ExecuteAsProperty,
2299        "FORMAT": FileFormatProperty,
2300        "LANGUAGE": LanguageProperty,
2301        "LOCATION": LocationProperty,
2302        "PARTITIONED_BY": PartitionedByProperty,
2303        "RETURNS": ReturnsProperty,
2304        "ROW_FORMAT": RowFormatProperty,
2305        "SORTKEY": SortKeyProperty,
2306    }
2307
2308    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2309
2310    # CREATE property locations
2311    # Form: schema specified
2312    #   create [POST_CREATE]
2313    #     table a [POST_NAME]
2314    #     (b int) [POST_SCHEMA]
2315    #     with ([POST_WITH])
2316    #     index (b) [POST_INDEX]
2317    #
2318    # Form: alias selection
2319    #   create [POST_CREATE]
2320    #     table a [POST_NAME]
2321    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2322    #     index (c) [POST_INDEX]
2323    class Location(AutoName):
2324        POST_CREATE = auto()
2325        POST_NAME = auto()
2326        POST_SCHEMA = auto()
2327        POST_WITH = auto()
2328        POST_ALIAS = auto()
2329        POST_EXPRESSION = auto()
2330        POST_INDEX = auto()
2331        UNSUPPORTED = auto()
2332
2333    @classmethod
2334    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2335        expressions = []
2336        for key, value in properties_dict.items():
2337            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2338            if property_cls:
2339                expressions.append(property_cls(this=convert(value)))
2340            else:
2341                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2342
2343        return cls(expressions=expressions)
2344
2345
2346class Qualify(Expression):
2347    pass
2348
2349
2350class InputOutputFormat(Expression):
2351    arg_types = {"input_format": False, "output_format": False}
2352
2353
2354# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2355class Return(Expression):
2356    pass
2357
2358
2359class Reference(Expression):
2360    arg_types = {"this": True, "expressions": False, "options": False}
2361
2362
2363class Tuple(Expression):
2364    arg_types = {"expressions": False}
2365
2366    def isin(
2367        self,
2368        *expressions: t.Any,
2369        query: t.Optional[ExpOrStr] = None,
2370        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2371        copy: bool = True,
2372        **opts,
2373    ) -> In:
2374        return In(
2375            this=maybe_copy(self, copy),
2376            expressions=[convert(e, copy=copy) for e in expressions],
2377            query=maybe_parse(query, copy=copy, **opts) if query else None,
2378            unnest=Unnest(
2379                expressions=[
2380                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2381                ]
2382            )
2383            if unnest
2384            else None,
2385        )
2386
2387
2388class Subqueryable(Unionable):
2389    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2390        """
2391        Convert this expression to an aliased expression that can be used as a Subquery.
2392
2393        Example:
2394            >>> subquery = Select().select("x").from_("tbl").subquery()
2395            >>> Select().select("x").from_(subquery).sql()
2396            'SELECT x FROM (SELECT x FROM tbl)'
2397
2398        Args:
2399            alias (str | Identifier): an optional alias for the subquery
2400            copy (bool): if `False`, modify this expression instance in-place.
2401
2402        Returns:
2403            Alias: the subquery
2404        """
2405        instance = maybe_copy(self, copy)
2406        if not isinstance(alias, Expression):
2407            alias = TableAlias(this=to_identifier(alias)) if alias else None
2408
2409        return Subquery(this=instance, alias=alias)
2410
2411    def limit(
2412        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2413    ) -> Select:
2414        raise NotImplementedError
2415
2416    @property
2417    def ctes(self):
2418        with_ = self.args.get("with")
2419        if not with_:
2420            return []
2421        return with_.expressions
2422
2423    @property
2424    def selects(self) -> t.List[Expression]:
2425        raise NotImplementedError("Subqueryable objects must implement `selects`")
2426
2427    @property
2428    def named_selects(self) -> t.List[str]:
2429        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2430
2431    def select(
2432        self,
2433        *expressions: t.Optional[ExpOrStr],
2434        append: bool = True,
2435        dialect: DialectType = None,
2436        copy: bool = True,
2437        **opts,
2438    ) -> Subqueryable:
2439        raise NotImplementedError("Subqueryable objects must implement `select`")
2440
2441    def with_(
2442        self,
2443        alias: ExpOrStr,
2444        as_: ExpOrStr,
2445        recursive: t.Optional[bool] = None,
2446        append: bool = True,
2447        dialect: DialectType = None,
2448        copy: bool = True,
2449        **opts,
2450    ) -> Subqueryable:
2451        """
2452        Append to or set the common table expressions.
2453
2454        Example:
2455            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2456            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2457
2458        Args:
2459            alias: the SQL code string to parse as the table name.
2460                If an `Expression` instance is passed, this is used as-is.
2461            as_: the SQL code string to parse as the table expression.
2462                If an `Expression` instance is passed, it will be used as-is.
2463            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2464            append: if `True`, add to any existing expressions.
2465                Otherwise, this resets the expressions.
2466            dialect: the dialect used to parse the input expression.
2467            copy: if `False`, modify this expression instance in-place.
2468            opts: other options to use to parse the input expressions.
2469
2470        Returns:
2471            The modified expression.
2472        """
2473        return _apply_cte_builder(
2474            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2475        )
2476
2477
2478QUERY_MODIFIERS = {
2479    "match": False,
2480    "laterals": False,
2481    "joins": False,
2482    "connect": False,
2483    "pivots": False,
2484    "where": False,
2485    "group": False,
2486    "having": False,
2487    "qualify": False,
2488    "windows": False,
2489    "distribute": False,
2490    "sort": False,
2491    "cluster": False,
2492    "order": False,
2493    "limit": False,
2494    "offset": False,
2495    "locks": False,
2496    "sample": False,
2497    "settings": False,
2498    "format": False,
2499}
2500
2501
2502# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2503class WithTableHint(Expression):
2504    arg_types = {"expressions": True}
2505
2506
2507# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2508class IndexTableHint(Expression):
2509    arg_types = {"this": True, "expressions": False, "target": False}
2510
2511
2512class Table(Expression):
2513    arg_types = {
2514        "this": True,
2515        "alias": False,
2516        "db": False,
2517        "catalog": False,
2518        "laterals": False,
2519        "joins": False,
2520        "pivots": False,
2521        "hints": False,
2522        "system_time": False,
2523        "version": False,
2524        "format": False,
2525        "pattern": False,
2526        "index": False,
2527        "ordinality": False,
2528    }
2529
2530    @property
2531    def name(self) -> str:
2532        if isinstance(self.this, Func):
2533            return ""
2534        return self.this.name
2535
2536    @property
2537    def db(self) -> str:
2538        return self.text("db")
2539
2540    @property
2541    def catalog(self) -> str:
2542        return self.text("catalog")
2543
2544    @property
2545    def selects(self) -> t.List[Expression]:
2546        return []
2547
2548    @property
2549    def named_selects(self) -> t.List[str]:
2550        return []
2551
2552    @property
2553    def parts(self) -> t.List[Expression]:
2554        """Return the parts of a table in order catalog, db, table."""
2555        parts: t.List[Expression] = []
2556
2557        for arg in ("catalog", "db", "this"):
2558            part = self.args.get(arg)
2559
2560            if isinstance(part, Dot):
2561                parts.extend(part.flatten())
2562            elif isinstance(part, Expression):
2563                parts.append(part)
2564
2565        return parts
2566
2567
2568class Union(Subqueryable):
2569    arg_types = {
2570        "with": False,
2571        "this": True,
2572        "expression": True,
2573        "distinct": False,
2574        "by_name": False,
2575        **QUERY_MODIFIERS,
2576    }
2577
2578    def limit(
2579        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2580    ) -> Select:
2581        """
2582        Set the LIMIT expression.
2583
2584        Example:
2585            >>> select("1").union(select("1")).limit(1).sql()
2586            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2587
2588        Args:
2589            expression: the SQL code string to parse.
2590                This can also be an integer.
2591                If a `Limit` instance is passed, this is used as-is.
2592                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2593            dialect: the dialect used to parse the input expression.
2594            copy: if `False`, modify this expression instance in-place.
2595            opts: other options to use to parse the input expressions.
2596
2597        Returns:
2598            The limited subqueryable.
2599        """
2600        return (
2601            select("*")
2602            .from_(self.subquery(alias="_l_0", copy=copy))
2603            .limit(expression, dialect=dialect, copy=False, **opts)
2604        )
2605
2606    def select(
2607        self,
2608        *expressions: t.Optional[ExpOrStr],
2609        append: bool = True,
2610        dialect: DialectType = None,
2611        copy: bool = True,
2612        **opts,
2613    ) -> Union:
2614        """Append to or set the SELECT of the union recursively.
2615
2616        Example:
2617            >>> from sqlglot import parse_one
2618            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2619            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2620
2621        Args:
2622            *expressions: the SQL code strings to parse.
2623                If an `Expression` instance is passed, it will be used as-is.
2624            append: if `True`, add to any existing expressions.
2625                Otherwise, this resets the expressions.
2626            dialect: the dialect used to parse the input expressions.
2627            copy: if `False`, modify this expression instance in-place.
2628            opts: other options to use to parse the input expressions.
2629
2630        Returns:
2631            Union: the modified expression.
2632        """
2633        this = self.copy() if copy else self
2634        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2635        this.expression.unnest().select(
2636            *expressions, append=append, dialect=dialect, copy=False, **opts
2637        )
2638        return this
2639
2640    @property
2641    def named_selects(self) -> t.List[str]:
2642        return self.this.unnest().named_selects
2643
2644    @property
2645    def is_star(self) -> bool:
2646        return self.this.is_star or self.expression.is_star
2647
2648    @property
2649    def selects(self) -> t.List[Expression]:
2650        return self.this.unnest().selects
2651
2652    @property
2653    def left(self) -> Expression:
2654        return self.this
2655
2656    @property
2657    def right(self) -> Expression:
2658        return self.expression
2659
2660
2661class Except(Union):
2662    pass
2663
2664
2665class Intersect(Union):
2666    pass
2667
2668
2669class Unnest(UDTF):
2670    arg_types = {
2671        "expressions": True,
2672        "alias": False,
2673        "offset": False,
2674    }
2675
2676
2677class Update(Expression):
2678    arg_types = {
2679        "with": False,
2680        "this": False,
2681        "expressions": True,
2682        "from": False,
2683        "where": False,
2684        "returning": False,
2685        "order": False,
2686        "limit": False,
2687    }
2688
2689
2690class Values(UDTF):
2691    arg_types = {"expressions": True, "alias": False}
2692
2693
2694class Var(Expression):
2695    pass
2696
2697
2698class Version(Expression):
2699    """
2700    Time travel, iceberg, bigquery etc
2701    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2702    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2703    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2704    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2705    this is either TIMESTAMP or VERSION
2706    kind is ("AS OF", "BETWEEN")
2707    """
2708
2709    arg_types = {"this": True, "kind": True, "expression": False}
2710
2711
2712class Schema(Expression):
2713    arg_types = {"this": False, "expressions": False}
2714
2715
2716# https://dev.mysql.com/doc/refman/8.0/en/select.html
2717# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2718class Lock(Expression):
2719    arg_types = {"update": True, "expressions": False, "wait": False}
2720
2721
2722class Select(Subqueryable):
2723    arg_types = {
2724        "with": False,
2725        "kind": False,
2726        "expressions": False,
2727        "hint": False,
2728        "distinct": False,
2729        "into": False,
2730        "from": False,
2731        **QUERY_MODIFIERS,
2732    }
2733
2734    def from_(
2735        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2736    ) -> Select:
2737        """
2738        Set the FROM expression.
2739
2740        Example:
2741            >>> Select().from_("tbl").select("x").sql()
2742            'SELECT x FROM tbl'
2743
2744        Args:
2745            expression : the SQL code strings to parse.
2746                If a `From` instance is passed, this is used as-is.
2747                If another `Expression` instance is passed, it will be wrapped in a `From`.
2748            dialect: the dialect used to parse the input expression.
2749            copy: if `False`, modify this expression instance in-place.
2750            opts: other options to use to parse the input expressions.
2751
2752        Returns:
2753            The modified Select expression.
2754        """
2755        return _apply_builder(
2756            expression=expression,
2757            instance=self,
2758            arg="from",
2759            into=From,
2760            prefix="FROM",
2761            dialect=dialect,
2762            copy=copy,
2763            **opts,
2764        )
2765
2766    def group_by(
2767        self,
2768        *expressions: t.Optional[ExpOrStr],
2769        append: bool = True,
2770        dialect: DialectType = None,
2771        copy: bool = True,
2772        **opts,
2773    ) -> Select:
2774        """
2775        Set the GROUP BY expression.
2776
2777        Example:
2778            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2779            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2780
2781        Args:
2782            *expressions: the SQL code strings to parse.
2783                If a `Group` instance is passed, this is used as-is.
2784                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2785                If nothing is passed in then a group by is not applied to the expression
2786            append: if `True`, add to any existing expressions.
2787                Otherwise, this flattens all the `Group` expression into a single expression.
2788            dialect: the dialect used to parse the input expression.
2789            copy: if `False`, modify this expression instance in-place.
2790            opts: other options to use to parse the input expressions.
2791
2792        Returns:
2793            The modified Select expression.
2794        """
2795        if not expressions:
2796            return self if not copy else self.copy()
2797
2798        return _apply_child_list_builder(
2799            *expressions,
2800            instance=self,
2801            arg="group",
2802            append=append,
2803            copy=copy,
2804            prefix="GROUP BY",
2805            into=Group,
2806            dialect=dialect,
2807            **opts,
2808        )
2809
2810    def order_by(
2811        self,
2812        *expressions: t.Optional[ExpOrStr],
2813        append: bool = True,
2814        dialect: DialectType = None,
2815        copy: bool = True,
2816        **opts,
2817    ) -> Select:
2818        """
2819        Set the ORDER BY expression.
2820
2821        Example:
2822            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2823            'SELECT x FROM tbl ORDER BY x DESC'
2824
2825        Args:
2826            *expressions: the SQL code strings to parse.
2827                If a `Group` instance is passed, this is used as-is.
2828                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2829            append: if `True`, add to any existing expressions.
2830                Otherwise, this flattens all the `Order` expression into a single expression.
2831            dialect: the dialect used to parse the input expression.
2832            copy: if `False`, modify this expression instance in-place.
2833            opts: other options to use to parse the input expressions.
2834
2835        Returns:
2836            The modified Select expression.
2837        """
2838        return _apply_child_list_builder(
2839            *expressions,
2840            instance=self,
2841            arg="order",
2842            append=append,
2843            copy=copy,
2844            prefix="ORDER BY",
2845            into=Order,
2846            dialect=dialect,
2847            **opts,
2848        )
2849
2850    def sort_by(
2851        self,
2852        *expressions: t.Optional[ExpOrStr],
2853        append: bool = True,
2854        dialect: DialectType = None,
2855        copy: bool = True,
2856        **opts,
2857    ) -> Select:
2858        """
2859        Set the SORT BY expression.
2860
2861        Example:
2862            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2863            'SELECT x FROM tbl SORT BY x DESC'
2864
2865        Args:
2866            *expressions: the SQL code strings to parse.
2867                If a `Group` instance is passed, this is used as-is.
2868                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2869            append: if `True`, add to any existing expressions.
2870                Otherwise, this flattens all the `Order` expression into a single expression.
2871            dialect: the dialect used to parse the input expression.
2872            copy: if `False`, modify this expression instance in-place.
2873            opts: other options to use to parse the input expressions.
2874
2875        Returns:
2876            The modified Select expression.
2877        """
2878        return _apply_child_list_builder(
2879            *expressions,
2880            instance=self,
2881            arg="sort",
2882            append=append,
2883            copy=copy,
2884            prefix="SORT BY",
2885            into=Sort,
2886            dialect=dialect,
2887            **opts,
2888        )
2889
2890    def cluster_by(
2891        self,
2892        *expressions: t.Optional[ExpOrStr],
2893        append: bool = True,
2894        dialect: DialectType = None,
2895        copy: bool = True,
2896        **opts,
2897    ) -> Select:
2898        """
2899        Set the CLUSTER BY expression.
2900
2901        Example:
2902            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2903            'SELECT x FROM tbl CLUSTER BY x DESC'
2904
2905        Args:
2906            *expressions: the SQL code strings to parse.
2907                If a `Group` instance is passed, this is used as-is.
2908                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2909            append: if `True`, add to any existing expressions.
2910                Otherwise, this flattens all the `Order` expression into a single expression.
2911            dialect: the dialect used to parse the input expression.
2912            copy: if `False`, modify this expression instance in-place.
2913            opts: other options to use to parse the input expressions.
2914
2915        Returns:
2916            The modified Select expression.
2917        """
2918        return _apply_child_list_builder(
2919            *expressions,
2920            instance=self,
2921            arg="cluster",
2922            append=append,
2923            copy=copy,
2924            prefix="CLUSTER BY",
2925            into=Cluster,
2926            dialect=dialect,
2927            **opts,
2928        )
2929
2930    def limit(
2931        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2932    ) -> Select:
2933        """
2934        Set the LIMIT expression.
2935
2936        Example:
2937            >>> Select().from_("tbl").select("x").limit(10).sql()
2938            'SELECT x FROM tbl LIMIT 10'
2939
2940        Args:
2941            expression: the SQL code string to parse.
2942                This can also be an integer.
2943                If a `Limit` instance is passed, this is used as-is.
2944                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2945            dialect: the dialect used to parse the input expression.
2946            copy: if `False`, modify this expression instance in-place.
2947            opts: other options to use to parse the input expressions.
2948
2949        Returns:
2950            Select: the modified expression.
2951        """
2952        return _apply_builder(
2953            expression=expression,
2954            instance=self,
2955            arg="limit",
2956            into=Limit,
2957            prefix="LIMIT",
2958            dialect=dialect,
2959            copy=copy,
2960            into_arg="expression",
2961            **opts,
2962        )
2963
2964    def offset(
2965        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2966    ) -> Select:
2967        """
2968        Set the OFFSET expression.
2969
2970        Example:
2971            >>> Select().from_("tbl").select("x").offset(10).sql()
2972            'SELECT x FROM tbl OFFSET 10'
2973
2974        Args:
2975            expression: the SQL code string to parse.
2976                This can also be an integer.
2977                If a `Offset` instance is passed, this is used as-is.
2978                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2979            dialect: the dialect used to parse the input expression.
2980            copy: if `False`, modify this expression instance in-place.
2981            opts: other options to use to parse the input expressions.
2982
2983        Returns:
2984            The modified Select expression.
2985        """
2986        return _apply_builder(
2987            expression=expression,
2988            instance=self,
2989            arg="offset",
2990            into=Offset,
2991            prefix="OFFSET",
2992            dialect=dialect,
2993            copy=copy,
2994            into_arg="expression",
2995            **opts,
2996        )
2997
2998    def select(
2999        self,
3000        *expressions: t.Optional[ExpOrStr],
3001        append: bool = True,
3002        dialect: DialectType = None,
3003        copy: bool = True,
3004        **opts,
3005    ) -> Select:
3006        """
3007        Append to or set the SELECT expressions.
3008
3009        Example:
3010            >>> Select().select("x", "y").sql()
3011            'SELECT x, y'
3012
3013        Args:
3014            *expressions: the SQL code strings to parse.
3015                If an `Expression` instance is passed, it will be used as-is.
3016            append: if `True`, add to any existing expressions.
3017                Otherwise, this resets the expressions.
3018            dialect: the dialect used to parse the input expressions.
3019            copy: if `False`, modify this expression instance in-place.
3020            opts: other options to use to parse the input expressions.
3021
3022        Returns:
3023            The modified Select expression.
3024        """
3025        return _apply_list_builder(
3026            *expressions,
3027            instance=self,
3028            arg="expressions",
3029            append=append,
3030            dialect=dialect,
3031            copy=copy,
3032            **opts,
3033        )
3034
3035    def lateral(
3036        self,
3037        *expressions: t.Optional[ExpOrStr],
3038        append: bool = True,
3039        dialect: DialectType = None,
3040        copy: bool = True,
3041        **opts,
3042    ) -> Select:
3043        """
3044        Append to or set the LATERAL expressions.
3045
3046        Example:
3047            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3048            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3049
3050        Args:
3051            *expressions: the SQL code strings to parse.
3052                If an `Expression` instance is passed, it will be used as-is.
3053            append: if `True`, add to any existing expressions.
3054                Otherwise, this resets the expressions.
3055            dialect: the dialect used to parse the input expressions.
3056            copy: if `False`, modify this expression instance in-place.
3057            opts: other options to use to parse the input expressions.
3058
3059        Returns:
3060            The modified Select expression.
3061        """
3062        return _apply_list_builder(
3063            *expressions,
3064            instance=self,
3065            arg="laterals",
3066            append=append,
3067            into=Lateral,
3068            prefix="LATERAL VIEW",
3069            dialect=dialect,
3070            copy=copy,
3071            **opts,
3072        )
3073
3074    def join(
3075        self,
3076        expression: ExpOrStr,
3077        on: t.Optional[ExpOrStr] = None,
3078        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3079        append: bool = True,
3080        join_type: t.Optional[str] = None,
3081        join_alias: t.Optional[Identifier | str] = None,
3082        dialect: DialectType = None,
3083        copy: bool = True,
3084        **opts,
3085    ) -> Select:
3086        """
3087        Append to or set the JOIN expressions.
3088
3089        Example:
3090            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3091            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3092
3093            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3094            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3095
3096            Use `join_type` to change the type of join:
3097
3098            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3099            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3100
3101        Args:
3102            expression: the SQL code string to parse.
3103                If an `Expression` instance is passed, it will be used as-is.
3104            on: optionally specify the join "on" criteria as a SQL string.
3105                If an `Expression` instance is passed, it will be used as-is.
3106            using: optionally specify the join "using" criteria as a SQL string.
3107                If an `Expression` instance is passed, it will be used as-is.
3108            append: if `True`, add to any existing expressions.
3109                Otherwise, this resets the expressions.
3110            join_type: if set, alter the parsed join type.
3111            join_alias: an optional alias for the joined source.
3112            dialect: the dialect used to parse the input expressions.
3113            copy: if `False`, modify this expression instance in-place.
3114            opts: other options to use to parse the input expressions.
3115
3116        Returns:
3117            Select: the modified expression.
3118        """
3119        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3120
3121        try:
3122            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3123        except ParseError:
3124            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3125
3126        join = expression if isinstance(expression, Join) else Join(this=expression)
3127
3128        if isinstance(join.this, Select):
3129            join.this.replace(join.this.subquery())
3130
3131        if join_type:
3132            method: t.Optional[Token]
3133            side: t.Optional[Token]
3134            kind: t.Optional[Token]
3135
3136            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3137
3138            if method:
3139                join.set("method", method.text)
3140            if side:
3141                join.set("side", side.text)
3142            if kind:
3143                join.set("kind", kind.text)
3144
3145        if on:
3146            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3147            join.set("on", on)
3148
3149        if using:
3150            join = _apply_list_builder(
3151                *ensure_list(using),
3152                instance=join,
3153                arg="using",
3154                append=append,
3155                copy=copy,
3156                into=Identifier,
3157                **opts,
3158            )
3159
3160        if join_alias:
3161            join.set("this", alias_(join.this, join_alias, table=True))
3162
3163        return _apply_list_builder(
3164            join,
3165            instance=self,
3166            arg="joins",
3167            append=append,
3168            copy=copy,
3169            **opts,
3170        )
3171
3172    def where(
3173        self,
3174        *expressions: t.Optional[ExpOrStr],
3175        append: bool = True,
3176        dialect: DialectType = None,
3177        copy: bool = True,
3178        **opts,
3179    ) -> Select:
3180        """
3181        Append to or set the WHERE expressions.
3182
3183        Example:
3184            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3185            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3186
3187        Args:
3188            *expressions: the SQL code strings to parse.
3189                If an `Expression` instance is passed, it will be used as-is.
3190                Multiple expressions are combined with an AND operator.
3191            append: if `True`, AND the new expressions to any existing expression.
3192                Otherwise, this resets the expression.
3193            dialect: the dialect used to parse the input expressions.
3194            copy: if `False`, modify this expression instance in-place.
3195            opts: other options to use to parse the input expressions.
3196
3197        Returns:
3198            Select: the modified expression.
3199        """
3200        return _apply_conjunction_builder(
3201            *expressions,
3202            instance=self,
3203            arg="where",
3204            append=append,
3205            into=Where,
3206            dialect=dialect,
3207            copy=copy,
3208            **opts,
3209        )
3210
3211    def having(
3212        self,
3213        *expressions: t.Optional[ExpOrStr],
3214        append: bool = True,
3215        dialect: DialectType = None,
3216        copy: bool = True,
3217        **opts,
3218    ) -> Select:
3219        """
3220        Append to or set the HAVING expressions.
3221
3222        Example:
3223            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3224            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3225
3226        Args:
3227            *expressions: the SQL code strings to parse.
3228                If an `Expression` instance is passed, it will be used as-is.
3229                Multiple expressions are combined with an AND operator.
3230            append: if `True`, AND the new expressions to any existing expression.
3231                Otherwise, this resets the expression.
3232            dialect: the dialect used to parse the input expressions.
3233            copy: if `False`, modify this expression instance in-place.
3234            opts: other options to use to parse the input expressions.
3235
3236        Returns:
3237            The modified Select expression.
3238        """
3239        return _apply_conjunction_builder(
3240            *expressions,
3241            instance=self,
3242            arg="having",
3243            append=append,
3244            into=Having,
3245            dialect=dialect,
3246            copy=copy,
3247            **opts,
3248        )
3249
3250    def window(
3251        self,
3252        *expressions: t.Optional[ExpOrStr],
3253        append: bool = True,
3254        dialect: DialectType = None,
3255        copy: bool = True,
3256        **opts,
3257    ) -> Select:
3258        return _apply_list_builder(
3259            *expressions,
3260            instance=self,
3261            arg="windows",
3262            append=append,
3263            into=Window,
3264            dialect=dialect,
3265            copy=copy,
3266            **opts,
3267        )
3268
3269    def qualify(
3270        self,
3271        *expressions: t.Optional[ExpOrStr],
3272        append: bool = True,
3273        dialect: DialectType = None,
3274        copy: bool = True,
3275        **opts,
3276    ) -> Select:
3277        return _apply_conjunction_builder(
3278            *expressions,
3279            instance=self,
3280            arg="qualify",
3281            append=append,
3282            into=Qualify,
3283            dialect=dialect,
3284            copy=copy,
3285            **opts,
3286        )
3287
3288    def distinct(
3289        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3290    ) -> Select:
3291        """
3292        Set the OFFSET expression.
3293
3294        Example:
3295            >>> Select().from_("tbl").select("x").distinct().sql()
3296            'SELECT DISTINCT x FROM tbl'
3297
3298        Args:
3299            ons: the expressions to distinct on
3300            distinct: whether the Select should be distinct
3301            copy: if `False`, modify this expression instance in-place.
3302
3303        Returns:
3304            Select: the modified expression.
3305        """
3306        instance = maybe_copy(self, copy)
3307        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3308        instance.set("distinct", Distinct(on=on) if distinct else None)
3309        return instance
3310
3311    def ctas(
3312        self,
3313        table: ExpOrStr,
3314        properties: t.Optional[t.Dict] = None,
3315        dialect: DialectType = None,
3316        copy: bool = True,
3317        **opts,
3318    ) -> Create:
3319        """
3320        Convert this expression to a CREATE TABLE AS statement.
3321
3322        Example:
3323            >>> Select().select("*").from_("tbl").ctas("x").sql()
3324            'CREATE TABLE x AS SELECT * FROM tbl'
3325
3326        Args:
3327            table: the SQL code string to parse as the table name.
3328                If another `Expression` instance is passed, it will be used as-is.
3329            properties: an optional mapping of table properties
3330            dialect: the dialect used to parse the input table.
3331            copy: if `False`, modify this expression instance in-place.
3332            opts: other options to use to parse the input table.
3333
3334        Returns:
3335            The new Create expression.
3336        """
3337        instance = maybe_copy(self, copy)
3338        table_expression = maybe_parse(
3339            table,
3340            into=Table,
3341            dialect=dialect,
3342            **opts,
3343        )
3344        properties_expression = None
3345        if properties:
3346            properties_expression = Properties.from_dict(properties)
3347
3348        return Create(
3349            this=table_expression,
3350            kind="table",
3351            expression=instance,
3352            properties=properties_expression,
3353        )
3354
3355    def lock(self, update: bool = True, copy: bool = True) -> Select:
3356        """
3357        Set the locking read mode for this expression.
3358
3359        Examples:
3360            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3361            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3362
3363            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3364            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3365
3366        Args:
3367            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3368            copy: if `False`, modify this expression instance in-place.
3369
3370        Returns:
3371            The modified expression.
3372        """
3373        inst = maybe_copy(self, copy)
3374        inst.set("locks", [Lock(update=update)])
3375
3376        return inst
3377
3378    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3379        """
3380        Set hints for this expression.
3381
3382        Examples:
3383            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3384            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3385
3386        Args:
3387            hints: The SQL code strings to parse as the hints.
3388                If an `Expression` instance is passed, it will be used as-is.
3389            dialect: The dialect used to parse the hints.
3390            copy: If `False`, modify this expression instance in-place.
3391
3392        Returns:
3393            The modified expression.
3394        """
3395        inst = maybe_copy(self, copy)
3396        inst.set(
3397            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3398        )
3399
3400        return inst
3401
3402    @property
3403    def named_selects(self) -> t.List[str]:
3404        return [e.output_name for e in self.expressions if e.alias_or_name]
3405
3406    @property
3407    def is_star(self) -> bool:
3408        return any(expression.is_star for expression in self.expressions)
3409
3410    @property
3411    def selects(self) -> t.List[Expression]:
3412        return self.expressions
3413
3414
3415class Subquery(DerivedTable, Unionable):
3416    arg_types = {
3417        "this": True,
3418        "alias": False,
3419        "with": False,
3420        **QUERY_MODIFIERS,
3421    }
3422
3423    def unnest(self):
3424        """
3425        Returns the first non subquery.
3426        """
3427        expression = self
3428        while isinstance(expression, Subquery):
3429            expression = expression.this
3430        return expression
3431
3432    def unwrap(self) -> Subquery:
3433        expression = self
3434        while expression.same_parent and expression.is_wrapper:
3435            expression = t.cast(Subquery, expression.parent)
3436        return expression
3437
3438    @property
3439    def is_wrapper(self) -> bool:
3440        """
3441        Whether this Subquery acts as a simple wrapper around another expression.
3442
3443        SELECT * FROM (((SELECT * FROM t)))
3444                      ^
3445                      This corresponds to a "wrapper" Subquery node
3446        """
3447        return all(v is None for k, v in self.args.items() if k != "this")
3448
3449    @property
3450    def is_star(self) -> bool:
3451        return self.this.is_star
3452
3453    @property
3454    def output_name(self) -> str:
3455        return self.alias
3456
3457
3458class TableSample(Expression):
3459    arg_types = {
3460        "this": False,
3461        "expressions": False,
3462        "method": False,
3463        "bucket_numerator": False,
3464        "bucket_denominator": False,
3465        "bucket_field": False,
3466        "percent": False,
3467        "rows": False,
3468        "size": False,
3469        "seed": False,
3470        "kind": False,
3471    }
3472
3473
3474class Tag(Expression):
3475    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3476
3477    arg_types = {
3478        "this": False,
3479        "prefix": False,
3480        "postfix": False,
3481    }
3482
3483
3484# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3485# https://duckdb.org/docs/sql/statements/pivot
3486class Pivot(Expression):
3487    arg_types = {
3488        "this": False,
3489        "alias": False,
3490        "expressions": False,
3491        "field": False,
3492        "unpivot": False,
3493        "using": False,
3494        "group": False,
3495        "columns": False,
3496        "include_nulls": False,
3497    }
3498
3499
3500class Window(Condition):
3501    arg_types = {
3502        "this": True,
3503        "partition_by": False,
3504        "order": False,
3505        "spec": False,
3506        "alias": False,
3507        "over": False,
3508        "first": False,
3509    }
3510
3511
3512class WindowSpec(Expression):
3513    arg_types = {
3514        "kind": False,
3515        "start": False,
3516        "start_side": False,
3517        "end": False,
3518        "end_side": False,
3519    }
3520
3521
3522class Where(Expression):
3523    pass
3524
3525
3526class Star(Expression):
3527    arg_types = {"except": False, "replace": False}
3528
3529    @property
3530    def name(self) -> str:
3531        return "*"
3532
3533    @property
3534    def output_name(self) -> str:
3535        return self.name
3536
3537
3538class Parameter(Condition):
3539    arg_types = {"this": True, "expression": False}
3540
3541
3542class SessionParameter(Condition):
3543    arg_types = {"this": True, "kind": False}
3544
3545
3546class Placeholder(Condition):
3547    arg_types = {"this": False, "kind": False}
3548
3549
3550class Null(Condition):
3551    arg_types: t.Dict[str, t.Any] = {}
3552
3553    @property
3554    def name(self) -> str:
3555        return "NULL"
3556
3557
3558class Boolean(Condition):
3559    pass
3560
3561
3562class DataTypeParam(Expression):
3563    arg_types = {"this": True, "expression": False}
3564
3565
3566class DataType(Expression):
3567    arg_types = {
3568        "this": True,
3569        "expressions": False,
3570        "nested": False,
3571        "values": False,
3572        "prefix": False,
3573        "kind": False,
3574    }
3575
3576    class Type(AutoName):
3577        ARRAY = auto()
3578        BIGDECIMAL = auto()
3579        BIGINT = auto()
3580        BIGSERIAL = auto()
3581        BINARY = auto()
3582        BIT = auto()
3583        BOOLEAN = auto()
3584        CHAR = auto()
3585        DATE = auto()
3586        DATEMULTIRANGE = auto()
3587        DATERANGE = auto()
3588        DATETIME = auto()
3589        DATETIME64 = auto()
3590        DECIMAL = auto()
3591        DOUBLE = auto()
3592        ENUM = auto()
3593        ENUM8 = auto()
3594        ENUM16 = auto()
3595        FIXEDSTRING = auto()
3596        FLOAT = auto()
3597        GEOGRAPHY = auto()
3598        GEOMETRY = auto()
3599        HLLSKETCH = auto()
3600        HSTORE = auto()
3601        IMAGE = auto()
3602        INET = auto()
3603        INT = auto()
3604        INT128 = auto()
3605        INT256 = auto()
3606        INT4MULTIRANGE = auto()
3607        INT4RANGE = auto()
3608        INT8MULTIRANGE = auto()
3609        INT8RANGE = auto()
3610        INTERVAL = auto()
3611        IPADDRESS = auto()
3612        IPPREFIX = auto()
3613        JSON = auto()
3614        JSONB = auto()
3615        LONGBLOB = auto()
3616        LONGTEXT = auto()
3617        LOWCARDINALITY = auto()
3618        MAP = auto()
3619        MEDIUMBLOB = auto()
3620        MEDIUMINT = auto()
3621        MEDIUMTEXT = auto()
3622        MONEY = auto()
3623        NCHAR = auto()
3624        NESTED = auto()
3625        NULL = auto()
3626        NULLABLE = auto()
3627        NUMMULTIRANGE = auto()
3628        NUMRANGE = auto()
3629        NVARCHAR = auto()
3630        OBJECT = auto()
3631        ROWVERSION = auto()
3632        SERIAL = auto()
3633        SET = auto()
3634        SMALLINT = auto()
3635        SMALLMONEY = auto()
3636        SMALLSERIAL = auto()
3637        STRUCT = auto()
3638        SUPER = auto()
3639        TEXT = auto()
3640        TINYBLOB = auto()
3641        TINYTEXT = auto()
3642        TIME = auto()
3643        TIMETZ = auto()
3644        TIMESTAMP = auto()
3645        TIMESTAMPLTZ = auto()
3646        TIMESTAMPTZ = auto()
3647        TIMESTAMP_S = auto()
3648        TIMESTAMP_MS = auto()
3649        TIMESTAMP_NS = auto()
3650        TINYINT = auto()
3651        TSMULTIRANGE = auto()
3652        TSRANGE = auto()
3653        TSTZMULTIRANGE = auto()
3654        TSTZRANGE = auto()
3655        UBIGINT = auto()
3656        UINT = auto()
3657        UINT128 = auto()
3658        UINT256 = auto()
3659        UMEDIUMINT = auto()
3660        UDECIMAL = auto()
3661        UNIQUEIDENTIFIER = auto()
3662        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3663        USERDEFINED = "USER-DEFINED"
3664        USMALLINT = auto()
3665        UTINYINT = auto()
3666        UUID = auto()
3667        VARBINARY = auto()
3668        VARCHAR = auto()
3669        VARIANT = auto()
3670        XML = auto()
3671        YEAR = auto()
3672
3673    TEXT_TYPES = {
3674        Type.CHAR,
3675        Type.NCHAR,
3676        Type.VARCHAR,
3677        Type.NVARCHAR,
3678        Type.TEXT,
3679    }
3680
3681    INTEGER_TYPES = {
3682        Type.INT,
3683        Type.TINYINT,
3684        Type.SMALLINT,
3685        Type.BIGINT,
3686        Type.INT128,
3687        Type.INT256,
3688    }
3689
3690    FLOAT_TYPES = {
3691        Type.FLOAT,
3692        Type.DOUBLE,
3693    }
3694
3695    NUMERIC_TYPES = {
3696        *INTEGER_TYPES,
3697        *FLOAT_TYPES,
3698    }
3699
3700    TEMPORAL_TYPES = {
3701        Type.TIME,
3702        Type.TIMETZ,
3703        Type.TIMESTAMP,
3704        Type.TIMESTAMPTZ,
3705        Type.TIMESTAMPLTZ,
3706        Type.TIMESTAMP_S,
3707        Type.TIMESTAMP_MS,
3708        Type.TIMESTAMP_NS,
3709        Type.DATE,
3710        Type.DATETIME,
3711        Type.DATETIME64,
3712    }
3713
3714    @classmethod
3715    def build(
3716        cls,
3717        dtype: str | DataType | DataType.Type,
3718        dialect: DialectType = None,
3719        udt: bool = False,
3720        **kwargs,
3721    ) -> DataType:
3722        """
3723        Constructs a DataType object.
3724
3725        Args:
3726            dtype: the data type of interest.
3727            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3728            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3729                DataType, thus creating a user-defined type.
3730            kawrgs: additional arguments to pass in the constructor of DataType.
3731
3732        Returns:
3733            The constructed DataType object.
3734        """
3735        from sqlglot import parse_one
3736
3737        if isinstance(dtype, str):
3738            if dtype.upper() == "UNKNOWN":
3739                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3740
3741            try:
3742                data_type_exp = parse_one(
3743                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3744                )
3745            except ParseError:
3746                if udt:
3747                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3748                raise
3749        elif isinstance(dtype, DataType.Type):
3750            data_type_exp = DataType(this=dtype)
3751        elif isinstance(dtype, DataType):
3752            return dtype
3753        else:
3754            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3755
3756        return DataType(**{**data_type_exp.args, **kwargs})
3757
3758    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3759        """
3760        Checks whether this DataType matches one of the provided data types. Nested types or precision
3761        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3762
3763        Args:
3764            dtypes: the data types to compare this DataType to.
3765
3766        Returns:
3767            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3768        """
3769        for dtype in dtypes:
3770            other = DataType.build(dtype, udt=True)
3771
3772            if (
3773                other.expressions
3774                or self.this == DataType.Type.USERDEFINED
3775                or other.this == DataType.Type.USERDEFINED
3776            ):
3777                matches = self == other
3778            else:
3779                matches = self.this == other.this
3780
3781            if matches:
3782                return True
3783        return False
3784
3785
3786# https://www.postgresql.org/docs/15/datatype-pseudo.html
3787class PseudoType(DataType):
3788    arg_types = {"this": True}
3789
3790
3791# https://www.postgresql.org/docs/15/datatype-oid.html
3792class ObjectIdentifier(DataType):
3793    arg_types = {"this": True}
3794
3795
3796# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3797class SubqueryPredicate(Predicate):
3798    pass
3799
3800
3801class All(SubqueryPredicate):
3802    pass
3803
3804
3805class Any(SubqueryPredicate):
3806    pass
3807
3808
3809class Exists(SubqueryPredicate):
3810    pass
3811
3812
3813# Commands to interact with the databases or engines. For most of the command
3814# expressions we parse whatever comes after the command's name as a string.
3815class Command(Expression):
3816    arg_types = {"this": True, "expression": False}
3817
3818
3819class Transaction(Expression):
3820    arg_types = {"this": False, "modes": False, "mark": False}
3821
3822
3823class Commit(Expression):
3824    arg_types = {"chain": False, "this": False, "durability": False}
3825
3826
3827class Rollback(Expression):
3828    arg_types = {"savepoint": False, "this": False}
3829
3830
3831class AlterTable(Expression):
3832    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3833
3834
3835class AddConstraint(Expression):
3836    arg_types = {"this": False, "expression": False, "enforced": False}
3837
3838
3839class DropPartition(Expression):
3840    arg_types = {"expressions": True, "exists": False}
3841
3842
3843# Binary expressions like (ADD a b)
3844class Binary(Condition):
3845    arg_types = {"this": True, "expression": True}
3846
3847    @property
3848    def left(self) -> Expression:
3849        return self.this
3850
3851    @property
3852    def right(self) -> Expression:
3853        return self.expression
3854
3855
3856class Add(Binary):
3857    pass
3858
3859
3860class Connector(Binary):
3861    pass
3862
3863
3864class And(Connector):
3865    pass
3866
3867
3868class Or(Connector):
3869    pass
3870
3871
3872class BitwiseAnd(Binary):
3873    pass
3874
3875
3876class BitwiseLeftShift(Binary):
3877    pass
3878
3879
3880class BitwiseOr(Binary):
3881    pass
3882
3883
3884class BitwiseRightShift(Binary):
3885    pass
3886
3887
3888class BitwiseXor(Binary):
3889    pass
3890
3891
3892class Div(Binary):
3893    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3894
3895
3896class Overlaps(Binary):
3897    pass
3898
3899
3900class Dot(Binary):
3901    @property
3902    def name(self) -> str:
3903        return self.expression.name
3904
3905    @property
3906    def output_name(self) -> str:
3907        return self.name
3908
3909    @classmethod
3910    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3911        """Build a Dot object with a sequence of expressions."""
3912        if len(expressions) < 2:
3913            raise ValueError(f"Dot requires >= 2 expressions.")
3914
3915        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3916
3917
3918class DPipe(Binary):
3919    pass
3920
3921
3922class SafeDPipe(DPipe):
3923    pass
3924
3925
3926class EQ(Binary, Predicate):
3927    pass
3928
3929
3930class NullSafeEQ(Binary, Predicate):
3931    pass
3932
3933
3934class NullSafeNEQ(Binary, Predicate):
3935    pass
3936
3937
3938# Represents e.g. := in DuckDB which is mostly used for setting parameters
3939class PropertyEQ(Binary):
3940    pass
3941
3942
3943class Distance(Binary):
3944    pass
3945
3946
3947class Escape(Binary):
3948    pass
3949
3950
3951class Glob(Binary, Predicate):
3952    pass
3953
3954
3955class GT(Binary, Predicate):
3956    pass
3957
3958
3959class GTE(Binary, Predicate):
3960    pass
3961
3962
3963class ILike(Binary, Predicate):
3964    pass
3965
3966
3967class ILikeAny(Binary, Predicate):
3968    pass
3969
3970
3971class IntDiv(Binary):
3972    pass
3973
3974
3975class Is(Binary, Predicate):
3976    pass
3977
3978
3979class Kwarg(Binary):
3980    """Kwarg in special functions like func(kwarg => y)."""
3981
3982
3983class Like(Binary, Predicate):
3984    pass
3985
3986
3987class LikeAny(Binary, Predicate):
3988    pass
3989
3990
3991class LT(Binary, Predicate):
3992    pass
3993
3994
3995class LTE(Binary, Predicate):
3996    pass
3997
3998
3999class Mod(Binary):
4000    pass
4001
4002
4003class Mul(Binary):
4004    pass
4005
4006
4007class NEQ(Binary, Predicate):
4008    pass
4009
4010
4011class SimilarTo(Binary, Predicate):
4012    pass
4013
4014
4015class Slice(Binary):
4016    arg_types = {"this": False, "expression": False}
4017
4018
4019class Sub(Binary):
4020    pass
4021
4022
4023class ArrayOverlaps(Binary):
4024    pass
4025
4026
4027# Unary Expressions
4028# (NOT a)
4029class Unary(Condition):
4030    pass
4031
4032
4033class BitwiseNot(Unary):
4034    pass
4035
4036
4037class Not(Unary):
4038    pass
4039
4040
4041class Paren(Unary):
4042    arg_types = {"this": True, "with": False}
4043
4044    @property
4045    def output_name(self) -> str:
4046        return self.this.name
4047
4048
4049class Neg(Unary):
4050    pass
4051
4052
4053class Alias(Expression):
4054    arg_types = {"this": True, "alias": False}
4055
4056    @property
4057    def output_name(self) -> str:
4058        return self.alias
4059
4060
4061class Aliases(Expression):
4062    arg_types = {"this": True, "expressions": True}
4063
4064    @property
4065    def aliases(self):
4066        return self.expressions
4067
4068
4069class AtTimeZone(Expression):
4070    arg_types = {"this": True, "zone": True}
4071
4072
4073class Between(Predicate):
4074    arg_types = {"this": True, "low": True, "high": True}
4075
4076
4077class Bracket(Condition):
4078    arg_types = {"this": True, "expressions": True}
4079
4080    @property
4081    def output_name(self) -> str:
4082        if len(self.expressions) == 1:
4083            return self.expressions[0].output_name
4084
4085        return super().output_name
4086
4087
4088class SafeBracket(Bracket):
4089    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4090
4091
4092class Distinct(Expression):
4093    arg_types = {"expressions": False, "on": False}
4094
4095
4096class In(Predicate):
4097    arg_types = {
4098        "this": True,
4099        "expressions": False,
4100        "query": False,
4101        "unnest": False,
4102        "field": False,
4103        "is_global": False,
4104    }
4105
4106
4107# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4108class ForIn(Expression):
4109    arg_types = {"this": True, "expression": True}
4110
4111
4112class TimeUnit(Expression):
4113    """Automatically converts unit arg into a var."""
4114
4115    arg_types = {"unit": False}
4116
4117    UNABBREVIATED_UNIT_NAME = {
4118        "d": "day",
4119        "h": "hour",
4120        "m": "minute",
4121        "ms": "millisecond",
4122        "ns": "nanosecond",
4123        "q": "quarter",
4124        "s": "second",
4125        "us": "microsecond",
4126        "w": "week",
4127        "y": "year",
4128    }
4129
4130    VAR_LIKE = (Column, Literal, Var)
4131
4132    def __init__(self, **args):
4133        unit = args.get("unit")
4134        if isinstance(unit, self.VAR_LIKE):
4135            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4136        elif isinstance(unit, Week):
4137            unit.set("this", Var(this=unit.this.name))
4138
4139        super().__init__(**args)
4140
4141    @property
4142    def unit(self) -> t.Optional[Var]:
4143        return self.args.get("unit")
4144
4145
4146class IntervalOp(TimeUnit):
4147    arg_types = {"unit": True, "expression": True}
4148
4149    def interval(self):
4150        return Interval(
4151            this=self.expression.copy(),
4152            unit=self.unit.copy(),
4153        )
4154
4155
4156# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4157# https://trino.io/docs/current/language/types.html#interval-day-to-second
4158# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4159class IntervalSpan(DataType):
4160    arg_types = {"this": True, "expression": True}
4161
4162
4163class Interval(TimeUnit):
4164    arg_types = {"this": False, "unit": False}
4165
4166
4167class IgnoreNulls(Expression):
4168    pass
4169
4170
4171class RespectNulls(Expression):
4172    pass
4173
4174
4175# Functions
4176class Func(Condition):
4177    """
4178    The base class for all function expressions.
4179
4180    Attributes:
4181        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4182            treated as a variable length argument and the argument's value will be stored as a list.
4183        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4184            for this function expression. These values are used to map this node to a name during parsing
4185            as well as to provide the function's name during SQL string generation. By default the SQL
4186            name is set to the expression's class name transformed to snake case.
4187    """
4188
4189    is_var_len_args = False
4190
4191    @classmethod
4192    def from_arg_list(cls, args):
4193        if cls.is_var_len_args:
4194            all_arg_keys = list(cls.arg_types)
4195            # If this function supports variable length argument treat the last argument as such.
4196            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4197            num_non_var = len(non_var_len_arg_keys)
4198
4199            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4200            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4201        else:
4202            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4203
4204        return cls(**args_dict)
4205
4206    @classmethod
4207    def sql_names(cls):
4208        if cls is Func:
4209            raise NotImplementedError(
4210                "SQL name is only supported by concrete function implementations"
4211            )
4212        if "_sql_names" not in cls.__dict__:
4213            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4214        return cls._sql_names
4215
4216    @classmethod
4217    def sql_name(cls):
4218        return cls.sql_names()[0]
4219
4220    @classmethod
4221    def default_parser_mappings(cls):
4222        return {name: cls.from_arg_list for name in cls.sql_names()}
4223
4224
4225class AggFunc(Func):
4226    pass
4227
4228
4229class ParameterizedAgg(AggFunc):
4230    arg_types = {"this": True, "expressions": True, "params": True}
4231
4232
4233class Abs(Func):
4234    pass
4235
4236
4237class ArgMax(AggFunc):
4238    arg_types = {"this": True, "expression": True, "count": False}
4239    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4240
4241
4242class ArgMin(AggFunc):
4243    arg_types = {"this": True, "expression": True, "count": False}
4244    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4245
4246
4247class ApproxTopK(AggFunc):
4248    arg_types = {"this": True, "expression": False, "counters": False}
4249
4250
4251class Flatten(Func):
4252    pass
4253
4254
4255# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4256class Transform(Func):
4257    arg_types = {"this": True, "expression": True}
4258
4259
4260class Anonymous(Func):
4261    arg_types = {"this": True, "expressions": False}
4262    is_var_len_args = True
4263
4264
4265# https://docs.snowflake.com/en/sql-reference/functions/hll
4266# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4267class Hll(AggFunc):
4268    arg_types = {"this": True, "expressions": False}
4269    is_var_len_args = True
4270
4271
4272class ApproxDistinct(AggFunc):
4273    arg_types = {"this": True, "accuracy": False}
4274    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4275
4276
4277class Array(Func):
4278    arg_types = {"expressions": False}
4279    is_var_len_args = True
4280
4281
4282# https://docs.snowflake.com/en/sql-reference/functions/to_char
4283class ToChar(Func):
4284    arg_types = {"this": True, "format": False}
4285
4286
4287class GenerateSeries(Func):
4288    arg_types = {"start": True, "end": True, "step": False}
4289
4290
4291class ArrayAgg(AggFunc):
4292    pass
4293
4294
4295class ArrayAll(Func):
4296    arg_types = {"this": True, "expression": True}
4297
4298
4299class ArrayAny(Func):
4300    arg_types = {"this": True, "expression": True}
4301
4302
4303class ArrayConcat(Func):
4304    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4305    arg_types = {"this": True, "expressions": False}
4306    is_var_len_args = True
4307
4308
4309class ArrayContains(Binary, Func):
4310    pass
4311
4312
4313class ArrayContained(Binary):
4314    pass
4315
4316
4317class ArrayFilter(Func):
4318    arg_types = {"this": True, "expression": True}
4319    _sql_names = ["FILTER", "ARRAY_FILTER"]
4320
4321
4322class ArrayJoin(Func):
4323    arg_types = {"this": True, "expression": True, "null": False}
4324
4325
4326class ArraySize(Func):
4327    arg_types = {"this": True, "expression": False}
4328
4329
4330class ArraySort(Func):
4331    arg_types = {"this": True, "expression": False}
4332
4333
4334class ArraySum(Func):
4335    pass
4336
4337
4338class ArrayUnionAgg(AggFunc):
4339    pass
4340
4341
4342class Avg(AggFunc):
4343    pass
4344
4345
4346class AnyValue(AggFunc):
4347    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4348
4349
4350class First(Func):
4351    arg_types = {"this": True, "ignore_nulls": False}
4352
4353
4354class Last(Func):
4355    arg_types = {"this": True, "ignore_nulls": False}
4356
4357
4358class Case(Func):
4359    arg_types = {"this": False, "ifs": True, "default": False}
4360
4361    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4362        instance = maybe_copy(self, copy)
4363        instance.append(
4364            "ifs",
4365            If(
4366                this=maybe_parse(condition, copy=copy, **opts),
4367                true=maybe_parse(then, copy=copy, **opts),
4368            ),
4369        )
4370        return instance
4371
4372    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4373        instance = maybe_copy(self, copy)
4374        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4375        return instance
4376
4377
4378class Cast(Func):
4379    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4380
4381    @property
4382    def name(self) -> str:
4383        return self.this.name
4384
4385    @property
4386    def to(self) -> DataType:
4387        return self.args["to"]
4388
4389    @property
4390    def output_name(self) -> str:
4391        return self.name
4392
4393    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4394        """
4395        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4396        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4397        array<int> != array<float>.
4398
4399        Args:
4400            dtypes: the data types to compare this Cast's DataType to.
4401
4402        Returns:
4403            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4404        """
4405        return self.to.is_type(*dtypes)
4406
4407
4408class TryCast(Cast):
4409    pass
4410
4411
4412class CastToStrType(Func):
4413    arg_types = {"this": True, "to": True}
4414
4415
4416class Collate(Binary, Func):
4417    pass
4418
4419
4420class Ceil(Func):
4421    arg_types = {"this": True, "decimals": False}
4422    _sql_names = ["CEIL", "CEILING"]
4423
4424
4425class Coalesce(Func):
4426    arg_types = {"this": True, "expressions": False}
4427    is_var_len_args = True
4428    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4429
4430
4431class Chr(Func):
4432    arg_types = {"this": True, "charset": False, "expressions": False}
4433    is_var_len_args = True
4434    _sql_names = ["CHR", "CHAR"]
4435
4436
4437class Concat(Func):
4438    arg_types = {"expressions": True}
4439    is_var_len_args = True
4440
4441
4442class SafeConcat(Concat):
4443    pass
4444
4445
4446class ConcatWs(Concat):
4447    _sql_names = ["CONCAT_WS"]
4448
4449
4450class Count(AggFunc):
4451    arg_types = {"this": False, "expressions": False}
4452    is_var_len_args = True
4453
4454
4455class CountIf(AggFunc):
4456    pass
4457
4458
4459class CurrentDate(Func):
4460    arg_types = {"this": False}
4461
4462
4463class CurrentDatetime(Func):
4464    arg_types = {"this": False}
4465
4466
4467class CurrentTime(Func):
4468    arg_types = {"this": False}
4469
4470
4471class CurrentTimestamp(Func):
4472    arg_types = {"this": False}
4473
4474
4475class CurrentUser(Func):
4476    arg_types = {"this": False}
4477
4478
4479class DateAdd(Func, IntervalOp):
4480    arg_types = {"this": True, "expression": True, "unit": False}
4481
4482
4483class DateSub(Func, IntervalOp):
4484    arg_types = {"this": True, "expression": True, "unit": False}
4485
4486
4487class DateDiff(Func, TimeUnit):
4488    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4489    arg_types = {"this": True, "expression": True, "unit": False}
4490
4491
4492class DateTrunc(Func):
4493    arg_types = {"unit": True, "this": True, "zone": False}
4494
4495    @property
4496    def unit(self) -> Expression:
4497        return self.args["unit"]
4498
4499
4500class DatetimeAdd(Func, IntervalOp):
4501    arg_types = {"this": True, "expression": True, "unit": False}
4502
4503
4504class DatetimeSub(Func, IntervalOp):
4505    arg_types = {"this": True, "expression": True, "unit": False}
4506
4507
4508class DatetimeDiff(Func, TimeUnit):
4509    arg_types = {"this": True, "expression": True, "unit": False}
4510
4511
4512class DatetimeTrunc(Func, TimeUnit):
4513    arg_types = {"this": True, "unit": True, "zone": False}
4514
4515
4516class DayOfWeek(Func):
4517    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4518
4519
4520class DayOfMonth(Func):
4521    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4522
4523
4524class DayOfYear(Func):
4525    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4526
4527
4528class ToDays(Func):
4529    pass
4530
4531
4532class WeekOfYear(Func):
4533    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4534
4535
4536class MonthsBetween(Func):
4537    arg_types = {"this": True, "expression": True, "roundoff": False}
4538
4539
4540class LastDateOfMonth(Func):
4541    pass
4542
4543
4544class Extract(Func):
4545    arg_types = {"this": True, "expression": True}
4546
4547
4548class Timestamp(Func):
4549    arg_types = {"this": False, "expression": False}
4550
4551
4552class TimestampAdd(Func, TimeUnit):
4553    arg_types = {"this": True, "expression": True, "unit": False}
4554
4555
4556class TimestampSub(Func, TimeUnit):
4557    arg_types = {"this": True, "expression": True, "unit": False}
4558
4559
4560class TimestampDiff(Func, TimeUnit):
4561    arg_types = {"this": True, "expression": True, "unit": False}
4562
4563
4564class TimestampTrunc(Func, TimeUnit):
4565    arg_types = {"this": True, "unit": True, "zone": False}
4566
4567
4568class TimeAdd(Func, TimeUnit):
4569    arg_types = {"this": True, "expression": True, "unit": False}
4570
4571
4572class TimeSub(Func, TimeUnit):
4573    arg_types = {"this": True, "expression": True, "unit": False}
4574
4575
4576class TimeDiff(Func, TimeUnit):
4577    arg_types = {"this": True, "expression": True, "unit": False}
4578
4579
4580class TimeTrunc(Func, TimeUnit):
4581    arg_types = {"this": True, "unit": True, "zone": False}
4582
4583
4584class DateFromParts(Func):
4585    _sql_names = ["DATEFROMPARTS"]
4586    arg_types = {"year": True, "month": True, "day": True}
4587
4588
4589class DateStrToDate(Func):
4590    pass
4591
4592
4593class DateToDateStr(Func):
4594    pass
4595
4596
4597class DateToDi(Func):
4598    pass
4599
4600
4601# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4602class Date(Func):
4603    arg_types = {"this": False, "zone": False, "expressions": False}
4604    is_var_len_args = True
4605
4606
4607class Day(Func):
4608    pass
4609
4610
4611class Decode(Func):
4612    arg_types = {"this": True, "charset": True, "replace": False}
4613
4614
4615class DiToDate(Func):
4616    pass
4617
4618
4619class Encode(Func):
4620    arg_types = {"this": True, "charset": True}
4621
4622
4623class Exp(Func):
4624    pass
4625
4626
4627# https://docs.snowflake.com/en/sql-reference/functions/flatten
4628class Explode(Func):
4629    arg_types = {"this": True, "expressions": False}
4630    is_var_len_args = True
4631
4632
4633class ExplodeOuter(Explode):
4634    pass
4635
4636
4637class Posexplode(Explode):
4638    pass
4639
4640
4641class PosexplodeOuter(Posexplode):
4642    pass
4643
4644
4645class Floor(Func):
4646    arg_types = {"this": True, "decimals": False}
4647
4648
4649class FromBase64(Func):
4650    pass
4651
4652
4653class ToBase64(Func):
4654    pass
4655
4656
4657class Greatest(Func):
4658    arg_types = {"this": True, "expressions": False}
4659    is_var_len_args = True
4660
4661
4662class GroupConcat(AggFunc):
4663    arg_types = {"this": True, "separator": False}
4664
4665
4666class Hex(Func):
4667    pass
4668
4669
4670class Xor(Connector, Func):
4671    arg_types = {"this": False, "expression": False, "expressions": False}
4672
4673
4674class If(Func):
4675    arg_types = {"this": True, "true": True, "false": False}
4676
4677
4678class Nullif(Func):
4679    arg_types = {"this": True, "expression": True}
4680
4681
4682class Initcap(Func):
4683    arg_types = {"this": True, "expression": False}
4684
4685
4686class IsNan(Func):
4687    _sql_names = ["IS_NAN", "ISNAN"]
4688
4689
4690class FormatJson(Expression):
4691    pass
4692
4693
4694class JSONKeyValue(Expression):
4695    arg_types = {"this": True, "expression": True}
4696
4697
4698class JSONObject(Func):
4699    arg_types = {
4700        "expressions": False,
4701        "null_handling": False,
4702        "unique_keys": False,
4703        "return_type": False,
4704        "encoding": False,
4705    }
4706
4707
4708# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4709class JSONArray(Func):
4710    arg_types = {
4711        "expressions": True,
4712        "null_handling": False,
4713        "return_type": False,
4714        "strict": False,
4715    }
4716
4717
4718# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4719class JSONArrayAgg(Func):
4720    arg_types = {
4721        "this": True,
4722        "order": False,
4723        "null_handling": False,
4724        "return_type": False,
4725        "strict": False,
4726    }
4727
4728
4729# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4730# Note: parsing of JSON column definitions is currently incomplete.
4731class JSONColumnDef(Expression):
4732    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4733
4734
4735class JSONSchema(Expression):
4736    arg_types = {"expressions": True}
4737
4738
4739# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4740class JSONTable(Func):
4741    arg_types = {
4742        "this": True,
4743        "schema": True,
4744        "path": False,
4745        "error_handling": False,
4746        "empty_handling": False,
4747    }
4748
4749
4750class OpenJSONColumnDef(Expression):
4751    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4752
4753
4754class OpenJSON(Func):
4755    arg_types = {"this": True, "path": False, "expressions": False}
4756
4757
4758class JSONBContains(Binary):
4759    _sql_names = ["JSONB_CONTAINS"]
4760
4761
4762class JSONExtract(Binary, Func):
4763    _sql_names = ["JSON_EXTRACT"]
4764
4765
4766class JSONExtractScalar(JSONExtract):
4767    _sql_names = ["JSON_EXTRACT_SCALAR"]
4768
4769
4770class JSONBExtract(JSONExtract):
4771    _sql_names = ["JSONB_EXTRACT"]
4772
4773
4774class JSONBExtractScalar(JSONExtract):
4775    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4776
4777
4778class JSONFormat(Func):
4779    arg_types = {"this": False, "options": False}
4780    _sql_names = ["JSON_FORMAT"]
4781
4782
4783# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4784class JSONArrayContains(Binary, Predicate, Func):
4785    _sql_names = ["JSON_ARRAY_CONTAINS"]
4786
4787
4788class ParseJSON(Func):
4789    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4790    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4791    arg_types = {"this": True, "expressions": False}
4792    is_var_len_args = True
4793
4794
4795class Least(Func):
4796    arg_types = {"this": True, "expressions": False}
4797    is_var_len_args = True
4798
4799
4800class Left(Func):
4801    arg_types = {"this": True, "expression": True}
4802
4803
4804class Right(Func):
4805    arg_types = {"this": True, "expression": True}
4806
4807
4808class Length(Func):
4809    _sql_names = ["LENGTH", "LEN"]
4810
4811
4812class Levenshtein(Func):
4813    arg_types = {
4814        "this": True,
4815        "expression": False,
4816        "ins_cost": False,
4817        "del_cost": False,
4818        "sub_cost": False,
4819    }
4820
4821
4822class Ln(Func):
4823    pass
4824
4825
4826class Log(Func):
4827    arg_types = {"this": True, "expression": False}
4828
4829
4830class Log2(Func):
4831    pass
4832
4833
4834class Log10(Func):
4835    pass
4836
4837
4838class LogicalOr(AggFunc):
4839    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4840
4841
4842class LogicalAnd(AggFunc):
4843    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4844
4845
4846class Lower(Func):
4847    _sql_names = ["LOWER", "LCASE"]
4848
4849
4850class Map(Func):
4851    arg_types = {"keys": False, "values": False}
4852
4853    @property
4854    def keys(self) -> t.List[Expression]:
4855        keys = self.args.get("keys")
4856        return keys.expressions if keys else []
4857
4858    @property
4859    def values(self) -> t.List[Expression]:
4860        values = self.args.get("values")
4861        return values.expressions if values else []
4862
4863
4864class MapFromEntries(Func):
4865    pass
4866
4867
4868class StarMap(Func):
4869    pass
4870
4871
4872class VarMap(Func):
4873    arg_types = {"keys": True, "values": True}
4874    is_var_len_args = True
4875
4876    @property
4877    def keys(self) -> t.List[Expression]:
4878        return self.args["keys"].expressions
4879
4880    @property
4881    def values(self) -> t.List[Expression]:
4882        return self.args["values"].expressions
4883
4884
4885# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4886class MatchAgainst(Func):
4887    arg_types = {"this": True, "expressions": True, "modifier": False}
4888
4889
4890class Max(AggFunc):
4891    arg_types = {"this": True, "expressions": False}
4892    is_var_len_args = True
4893
4894
4895class MD5(Func):
4896    _sql_names = ["MD5"]
4897
4898
4899# Represents the variant of the MD5 function that returns a binary value
4900class MD5Digest(Func):
4901    _sql_names = ["MD5_DIGEST"]
4902
4903
4904class Min(AggFunc):
4905    arg_types = {"this": True, "expressions": False}
4906    is_var_len_args = True
4907
4908
4909class Month(Func):
4910    pass
4911
4912
4913class Nvl2(Func):
4914    arg_types = {"this": True, "true": True, "false": False}
4915
4916
4917# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4918class Predict(Func):
4919    arg_types = {"this": True, "expression": True, "params_struct": False}
4920
4921
4922class Pow(Binary, Func):
4923    _sql_names = ["POWER", "POW"]
4924
4925
4926class PercentileCont(AggFunc):
4927    arg_types = {"this": True, "expression": False}
4928
4929
4930class PercentileDisc(AggFunc):
4931    arg_types = {"this": True, "expression": False}
4932
4933
4934class Quantile(AggFunc):
4935    arg_types = {"this": True, "quantile": True}
4936
4937
4938class ApproxQuantile(Quantile):
4939    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4940
4941
4942class RangeN(Func):
4943    arg_types = {"this": True, "expressions": True, "each": False}
4944
4945
4946class ReadCSV(Func):
4947    _sql_names = ["READ_CSV"]
4948    is_var_len_args = True
4949    arg_types = {"this": True, "expressions": False}
4950
4951
4952class Reduce(Func):
4953    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4954
4955
4956class RegexpExtract(Func):
4957    arg_types = {
4958        "this": True,
4959        "expression": True,
4960        "position": False,
4961        "occurrence": False,
4962        "parameters": False,
4963        "group": False,
4964    }
4965
4966
4967class RegexpReplace(Func):
4968    arg_types = {
4969        "this": True,
4970        "expression": True,
4971        "replacement": True,
4972        "position": False,
4973        "occurrence": False,
4974        "parameters": False,
4975        "modifiers": False,
4976    }
4977
4978
4979class RegexpLike(Binary, Func):
4980    arg_types = {"this": True, "expression": True, "flag": False}
4981
4982
4983class RegexpILike(Binary, Func):
4984    arg_types = {"this": True, "expression": True, "flag": False}
4985
4986
4987# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4988# limit is the number of times a pattern is applied
4989class RegexpSplit(Func):
4990    arg_types = {"this": True, "expression": True, "limit": False}
4991
4992
4993class Repeat(Func):
4994    arg_types = {"this": True, "times": True}
4995
4996
4997class Round(Func):
4998    arg_types = {"this": True, "decimals": False}
4999
5000
5001class RowNumber(Func):
5002    arg_types: t.Dict[str, t.Any] = {}
5003
5004
5005class SafeDivide(Func):
5006    arg_types = {"this": True, "expression": True}
5007
5008
5009class SetAgg(AggFunc):
5010    pass
5011
5012
5013class SHA(Func):
5014    _sql_names = ["SHA", "SHA1"]
5015
5016
5017class SHA2(Func):
5018    _sql_names = ["SHA2"]
5019    arg_types = {"this": True, "length": False}
5020
5021
5022class SortArray(Func):
5023    arg_types = {"this": True, "asc": False}
5024
5025
5026class Split(Func):
5027    arg_types = {"this": True, "expression": True, "limit": False}
5028
5029
5030# Start may be omitted in the case of postgres
5031# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5032class Substring(Func):
5033    arg_types = {"this": True, "start": False, "length": False}
5034
5035
5036class StandardHash(Func):
5037    arg_types = {"this": True, "expression": False}
5038
5039
5040class StartsWith(Func):
5041    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5042    arg_types = {"this": True, "expression": True}
5043
5044
5045class StrPosition(Func):
5046    arg_types = {
5047        "this": True,
5048        "substr": True,
5049        "position": False,
5050        "instance": False,
5051    }
5052
5053
5054class StrToDate(Func):
5055    arg_types = {"this": True, "format": True}
5056
5057
5058class StrToTime(Func):
5059    arg_types = {"this": True, "format": True, "zone": False}
5060
5061
5062# Spark allows unix_timestamp()
5063# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5064class StrToUnix(Func):
5065    arg_types = {"this": False, "format": False}
5066
5067
5068# https://prestodb.io/docs/current/functions/string.html
5069# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5070class StrToMap(Func):
5071    arg_types = {
5072        "this": True,
5073        "pair_delim": False,
5074        "key_value_delim": False,
5075        "duplicate_resolution_callback": False,
5076    }
5077
5078
5079class NumberToStr(Func):
5080    arg_types = {"this": True, "format": True, "culture": False}
5081
5082
5083class FromBase(Func):
5084    arg_types = {"this": True, "expression": True}
5085
5086
5087class Struct(Func):
5088    arg_types = {"expressions": False}
5089    is_var_len_args = True
5090
5091
5092class StructExtract(Func):
5093    arg_types = {"this": True, "expression": True}
5094
5095
5096# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5097# https://docs.snowflake.com/en/sql-reference/functions/insert
5098class Stuff(Func):
5099    _sql_names = ["STUFF", "INSERT"]
5100    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5101
5102
5103class Sum(AggFunc):
5104    pass
5105
5106
5107class Sqrt(Func):
5108    pass
5109
5110
5111class Stddev(AggFunc):
5112    pass
5113
5114
5115class StddevPop(AggFunc):
5116    pass
5117
5118
5119class StddevSamp(AggFunc):
5120    pass
5121
5122
5123class TimeToStr(Func):
5124    arg_types = {"this": True, "format": True, "culture": False}
5125
5126
5127class TimeToTimeStr(Func):
5128    pass
5129
5130
5131class TimeToUnix(Func):
5132    pass
5133
5134
5135class TimeStrToDate(Func):
5136    pass
5137
5138
5139class TimeStrToTime(Func):
5140    pass
5141
5142
5143class TimeStrToUnix(Func):
5144    pass
5145
5146
5147class Trim(Func):
5148    arg_types = {
5149        "this": True,
5150        "expression": False,
5151        "position": False,
5152        "collation": False,
5153    }
5154
5155
5156class TsOrDsAdd(Func, TimeUnit):
5157    arg_types = {"this": True, "expression": True, "unit": False}
5158
5159
5160class TsOrDsToDateStr(Func):
5161    pass
5162
5163
5164class TsOrDsToDate(Func):
5165    arg_types = {"this": True, "format": False}
5166
5167
5168class TsOrDiToDi(Func):
5169    pass
5170
5171
5172class Unhex(Func):
5173    pass
5174
5175
5176class UnixToStr(Func):
5177    arg_types = {"this": True, "format": False}
5178
5179
5180# https://prestodb.io/docs/current/functions/datetime.html
5181# presto has weird zone/hours/minutes
5182class UnixToTime(Func):
5183    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5184
5185    SECONDS = Literal.string("seconds")
5186    MILLIS = Literal.string("millis")
5187    MICROS = Literal.string("micros")
5188
5189
5190class UnixToTimeStr(Func):
5191    pass
5192
5193
5194class Upper(Func):
5195    _sql_names = ["UPPER", "UCASE"]
5196
5197
5198class Variance(AggFunc):
5199    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5200
5201
5202class VariancePop(AggFunc):
5203    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5204
5205
5206class Week(Func):
5207    arg_types = {"this": True, "mode": False}
5208
5209
5210class XMLTable(Func):
5211    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5212
5213
5214class Year(Func):
5215    pass
5216
5217
5218class Use(Expression):
5219    arg_types = {"this": True, "kind": False}
5220
5221
5222class Merge(Expression):
5223    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5224
5225
5226class When(Func):
5227    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5228
5229
5230# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5231# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5232class NextValueFor(Func):
5233    arg_types = {"this": True, "order": False}
5234
5235
5236def _norm_arg(arg):
5237    return arg.lower() if type(arg) is str else arg
5238
5239
5240ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5241
5242
5243# Helpers
5244@t.overload
5245def maybe_parse(
5246    sql_or_expression: ExpOrStr,
5247    *,
5248    into: t.Type[E],
5249    dialect: DialectType = None,
5250    prefix: t.Optional[str] = None,
5251    copy: bool = False,
5252    **opts,
5253) -> E:
5254    ...
5255
5256
5257@t.overload
5258def maybe_parse(
5259    sql_or_expression: str | E,
5260    *,
5261    into: t.Optional[IntoType] = None,
5262    dialect: DialectType = None,
5263    prefix: t.Optional[str] = None,
5264    copy: bool = False,
5265    **opts,
5266) -> E:
5267    ...
5268
5269
5270def maybe_parse(
5271    sql_or_expression: ExpOrStr,
5272    *,
5273    into: t.Optional[IntoType] = None,
5274    dialect: DialectType = None,
5275    prefix: t.Optional[str] = None,
5276    copy: bool = False,
5277    **opts,
5278) -> Expression:
5279    """Gracefully handle a possible string or expression.
5280
5281    Example:
5282        >>> maybe_parse("1")
5283        (LITERAL this: 1, is_string: False)
5284        >>> maybe_parse(to_identifier("x"))
5285        (IDENTIFIER this: x, quoted: False)
5286
5287    Args:
5288        sql_or_expression: the SQL code string or an expression
5289        into: the SQLGlot Expression to parse into
5290        dialect: the dialect used to parse the input expressions (in the case that an
5291            input expression is a SQL string).
5292        prefix: a string to prefix the sql with before it gets parsed
5293            (automatically includes a space)
5294        copy: whether or not to copy the expression.
5295        **opts: other options to use to parse the input expressions (again, in the case
5296            that an input expression is a SQL string).
5297
5298    Returns:
5299        Expression: the parsed or given expression.
5300    """
5301    if isinstance(sql_or_expression, Expression):
5302        if copy:
5303            return sql_or_expression.copy()
5304        return sql_or_expression
5305
5306    if sql_or_expression is None:
5307        raise ParseError(f"SQL cannot be None")
5308
5309    import sqlglot
5310
5311    sql = str(sql_or_expression)
5312    if prefix:
5313        sql = f"{prefix} {sql}"
5314
5315    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5316
5317
5318@t.overload
5319def maybe_copy(instance: None, copy: bool = True) -> None:
5320    ...
5321
5322
5323@t.overload
5324def maybe_copy(instance: E, copy: bool = True) -> E:
5325    ...
5326
5327
5328def maybe_copy(instance, copy=True):
5329    return instance.copy() if copy and instance else instance
5330
5331
5332def _is_wrong_expression(expression, into):
5333    return isinstance(expression, Expression) and not isinstance(expression, into)
5334
5335
5336def _apply_builder(
5337    expression,
5338    instance,
5339    arg,
5340    copy=True,
5341    prefix=None,
5342    into=None,
5343    dialect=None,
5344    into_arg="this",
5345    **opts,
5346):
5347    if _is_wrong_expression(expression, into):
5348        expression = into(**{into_arg: expression})
5349    instance = maybe_copy(instance, copy)
5350    expression = maybe_parse(
5351        sql_or_expression=expression,
5352        prefix=prefix,
5353        into=into,
5354        dialect=dialect,
5355        **opts,
5356    )
5357    instance.set(arg, expression)
5358    return instance
5359
5360
5361def _apply_child_list_builder(
5362    *expressions,
5363    instance,
5364    arg,
5365    append=True,
5366    copy=True,
5367    prefix=None,
5368    into=None,
5369    dialect=None,
5370    properties=None,
5371    **opts,
5372):
5373    instance = maybe_copy(instance, copy)
5374    parsed = []
5375    for expression in expressions:
5376        if expression is not None:
5377            if _is_wrong_expression(expression, into):
5378                expression = into(expressions=[expression])
5379
5380            expression = maybe_parse(
5381                expression,
5382                into=into,
5383                dialect=dialect,
5384                prefix=prefix,
5385                **opts,
5386            )
5387            parsed.extend(expression.expressions)
5388
5389    existing = instance.args.get(arg)
5390    if append and existing:
5391        parsed = existing.expressions + parsed
5392
5393    child = into(expressions=parsed)
5394    for k, v in (properties or {}).items():
5395        child.set(k, v)
5396    instance.set(arg, child)
5397
5398    return instance
5399
5400
5401def _apply_list_builder(
5402    *expressions,
5403    instance,
5404    arg,
5405    append=True,
5406    copy=True,
5407    prefix=None,
5408    into=None,
5409    dialect=None,
5410    **opts,
5411):
5412    inst = maybe_copy(instance, copy)
5413
5414    expressions = [
5415        maybe_parse(
5416            sql_or_expression=expression,
5417            into=into,
5418            prefix=prefix,
5419            dialect=dialect,
5420            **opts,
5421        )
5422        for expression in expressions
5423        if expression is not None
5424    ]
5425
5426    existing_expressions = inst.args.get(arg)
5427    if append and existing_expressions:
5428        expressions = existing_expressions + expressions
5429
5430    inst.set(arg, expressions)
5431    return inst
5432
5433
5434def _apply_conjunction_builder(
5435    *expressions,
5436    instance,
5437    arg,
5438    into=None,
5439    append=True,
5440    copy=True,
5441    dialect=None,
5442    **opts,
5443):
5444    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5445    if not expressions:
5446        return instance
5447
5448    inst = maybe_copy(instance, copy)
5449
5450    existing = inst.args.get(arg)
5451    if append and existing is not None:
5452        expressions = [existing.this if into else existing] + list(expressions)
5453
5454    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5455
5456    inst.set(arg, into(this=node) if into else node)
5457    return inst
5458
5459
5460def _apply_cte_builder(
5461    instance: E,
5462    alias: ExpOrStr,
5463    as_: ExpOrStr,
5464    recursive: t.Optional[bool] = None,
5465    append: bool = True,
5466    dialect: DialectType = None,
5467    copy: bool = True,
5468    **opts,
5469) -> E:
5470    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5471    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5472    cte = CTE(this=as_expression, alias=alias_expression)
5473    return _apply_child_list_builder(
5474        cte,
5475        instance=instance,
5476        arg="with",
5477        append=append,
5478        copy=copy,
5479        into=With,
5480        properties={"recursive": recursive or False},
5481    )
5482
5483
5484def _combine(
5485    expressions: t.Sequence[t.Optional[ExpOrStr]],
5486    operator: t.Type[Connector],
5487    dialect: DialectType = None,
5488    copy: bool = True,
5489    **opts,
5490) -> Expression:
5491    conditions = [
5492        condition(expression, dialect=dialect, copy=copy, **opts)
5493        for expression in expressions
5494        if expression is not None
5495    ]
5496
5497    this, *rest = conditions
5498    if rest:
5499        this = _wrap(this, Connector)
5500    for expression in rest:
5501        this = operator(this=this, expression=_wrap(expression, Connector))
5502
5503    return this
5504
5505
5506def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5507    return Paren(this=expression) if isinstance(expression, kind) else expression
5508
5509
5510def union(
5511    left: ExpOrStr,
5512    right: ExpOrStr,
5513    distinct: bool = True,
5514    dialect: DialectType = None,
5515    copy: bool = True,
5516    **opts,
5517) -> Union:
5518    """
5519    Initializes a syntax tree from one UNION expression.
5520
5521    Example:
5522        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5523        'SELECT * FROM foo UNION SELECT * FROM bla'
5524
5525    Args:
5526        left: the SQL code string corresponding to the left-hand side.
5527            If an `Expression` instance is passed, it will be used as-is.
5528        right: the SQL code string corresponding to the right-hand side.
5529            If an `Expression` instance is passed, it will be used as-is.
5530        distinct: set the DISTINCT flag if and only if this is true.
5531        dialect: the dialect used to parse the input expression.
5532        copy: whether or not to copy the expression.
5533        opts: other options to use to parse the input expressions.
5534
5535    Returns:
5536        The new Union instance.
5537    """
5538    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5539    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5540
5541    return Union(this=left, expression=right, distinct=distinct)
5542
5543
5544def intersect(
5545    left: ExpOrStr,
5546    right: ExpOrStr,
5547    distinct: bool = True,
5548    dialect: DialectType = None,
5549    copy: bool = True,
5550    **opts,
5551) -> Intersect:
5552    """
5553    Initializes a syntax tree from one INTERSECT expression.
5554
5555    Example:
5556        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5557        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5558
5559    Args:
5560        left: the SQL code string corresponding to the left-hand side.
5561            If an `Expression` instance is passed, it will be used as-is.
5562        right: the SQL code string corresponding to the right-hand side.
5563            If an `Expression` instance is passed, it will be used as-is.
5564        distinct: set the DISTINCT flag if and only if this is true.
5565        dialect: the dialect used to parse the input expression.
5566        copy: whether or not to copy the expression.
5567        opts: other options to use to parse the input expressions.
5568
5569    Returns:
5570        The new Intersect instance.
5571    """
5572    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5573    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5574
5575    return Intersect(this=left, expression=right, distinct=distinct)
5576
5577
5578def except_(
5579    left: ExpOrStr,
5580    right: ExpOrStr,
5581    distinct: bool = True,
5582    dialect: DialectType = None,
5583    copy: bool = True,
5584    **opts,
5585) -> Except:
5586    """
5587    Initializes a syntax tree from one EXCEPT expression.
5588
5589    Example:
5590        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5591        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5592
5593    Args:
5594        left: the SQL code string corresponding to the left-hand side.
5595            If an `Expression` instance is passed, it will be used as-is.
5596        right: the SQL code string corresponding to the right-hand side.
5597            If an `Expression` instance is passed, it will be used as-is.
5598        distinct: set the DISTINCT flag if and only if this is true.
5599        dialect: the dialect used to parse the input expression.
5600        copy: whether or not to copy the expression.
5601        opts: other options to use to parse the input expressions.
5602
5603    Returns:
5604        The new Except instance.
5605    """
5606    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5607    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5608
5609    return Except(this=left, expression=right, distinct=distinct)
5610
5611
5612def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5613    """
5614    Initializes a syntax tree from one or multiple SELECT expressions.
5615
5616    Example:
5617        >>> select("col1", "col2").from_("tbl").sql()
5618        'SELECT col1, col2 FROM tbl'
5619
5620    Args:
5621        *expressions: the SQL code string to parse as the expressions of a
5622            SELECT statement. If an Expression instance is passed, this is used as-is.
5623        dialect: the dialect used to parse the input expressions (in the case that an
5624            input expression is a SQL string).
5625        **opts: other options to use to parse the input expressions (again, in the case
5626            that an input expression is a SQL string).
5627
5628    Returns:
5629        Select: the syntax tree for the SELECT statement.
5630    """
5631    return Select().select(*expressions, dialect=dialect, **opts)
5632
5633
5634def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5635    """
5636    Initializes a syntax tree from a FROM expression.
5637
5638    Example:
5639        >>> from_("tbl").select("col1", "col2").sql()
5640        'SELECT col1, col2 FROM tbl'
5641
5642    Args:
5643        *expression: the SQL code string to parse as the FROM expressions of a
5644            SELECT statement. If an Expression instance is passed, this is used as-is.
5645        dialect: the dialect used to parse the input expression (in the case that the
5646            input expression is a SQL string).
5647        **opts: other options to use to parse the input expressions (again, in the case
5648            that the input expression is a SQL string).
5649
5650    Returns:
5651        Select: the syntax tree for the SELECT statement.
5652    """
5653    return Select().from_(expression, dialect=dialect, **opts)
5654
5655
5656def update(
5657    table: str | Table,
5658    properties: dict,
5659    where: t.Optional[ExpOrStr] = None,
5660    from_: t.Optional[ExpOrStr] = None,
5661    dialect: DialectType = None,
5662    **opts,
5663) -> Update:
5664    """
5665    Creates an update statement.
5666
5667    Example:
5668        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5669        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5670
5671    Args:
5672        *properties: dictionary of properties to set which are
5673            auto converted to sql objects eg None -> NULL
5674        where: sql conditional parsed into a WHERE statement
5675        from_: sql statement parsed into a FROM statement
5676        dialect: the dialect used to parse the input expressions.
5677        **opts: other options to use to parse the input expressions.
5678
5679    Returns:
5680        Update: the syntax tree for the UPDATE statement.
5681    """
5682    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5683    update_expr.set(
5684        "expressions",
5685        [
5686            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5687            for k, v in properties.items()
5688        ],
5689    )
5690    if from_:
5691        update_expr.set(
5692            "from",
5693            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5694        )
5695    if isinstance(where, Condition):
5696        where = Where(this=where)
5697    if where:
5698        update_expr.set(
5699            "where",
5700            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5701        )
5702    return update_expr
5703
5704
5705def delete(
5706    table: ExpOrStr,
5707    where: t.Optional[ExpOrStr] = None,
5708    returning: t.Optional[ExpOrStr] = None,
5709    dialect: DialectType = None,
5710    **opts,
5711) -> Delete:
5712    """
5713    Builds a delete statement.
5714
5715    Example:
5716        >>> delete("my_table", where="id > 1").sql()
5717        'DELETE FROM my_table WHERE id > 1'
5718
5719    Args:
5720        where: sql conditional parsed into a WHERE statement
5721        returning: sql conditional parsed into a RETURNING statement
5722        dialect: the dialect used to parse the input expressions.
5723        **opts: other options to use to parse the input expressions.
5724
5725    Returns:
5726        Delete: the syntax tree for the DELETE statement.
5727    """
5728    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5729    if where:
5730        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5731    if returning:
5732        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5733    return delete_expr
5734
5735
5736def insert(
5737    expression: ExpOrStr,
5738    into: ExpOrStr,
5739    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5740    overwrite: t.Optional[bool] = None,
5741    dialect: DialectType = None,
5742    copy: bool = True,
5743    **opts,
5744) -> Insert:
5745    """
5746    Builds an INSERT statement.
5747
5748    Example:
5749        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5750        'INSERT INTO tbl VALUES (1, 2, 3)'
5751
5752    Args:
5753        expression: the sql string or expression of the INSERT statement
5754        into: the tbl to insert data to.
5755        columns: optionally the table's column names.
5756        overwrite: whether to INSERT OVERWRITE or not.
5757        dialect: the dialect used to parse the input expressions.
5758        copy: whether or not to copy the expression.
5759        **opts: other options to use to parse the input expressions.
5760
5761    Returns:
5762        Insert: the syntax tree for the INSERT statement.
5763    """
5764    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5765    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5766
5767    if columns:
5768        this = _apply_list_builder(
5769            *columns,
5770            instance=Schema(this=this),
5771            arg="expressions",
5772            into=Identifier,
5773            copy=False,
5774            dialect=dialect,
5775            **opts,
5776        )
5777
5778    return Insert(this=this, expression=expr, overwrite=overwrite)
5779
5780
5781def condition(
5782    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5783) -> Condition:
5784    """
5785    Initialize a logical condition expression.
5786
5787    Example:
5788        >>> condition("x=1").sql()
5789        'x = 1'
5790
5791        This is helpful for composing larger logical syntax trees:
5792        >>> where = condition("x=1")
5793        >>> where = where.and_("y=1")
5794        >>> Select().from_("tbl").select("*").where(where).sql()
5795        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5796
5797    Args:
5798        *expression: the SQL code string to parse.
5799            If an Expression instance is passed, this is used as-is.
5800        dialect: the dialect used to parse the input expression (in the case that the
5801            input expression is a SQL string).
5802        copy: Whether or not to copy `expression` (only applies to expressions).
5803        **opts: other options to use to parse the input expressions (again, in the case
5804            that the input expression is a SQL string).
5805
5806    Returns:
5807        The new Condition instance
5808    """
5809    return maybe_parse(
5810        expression,
5811        into=Condition,
5812        dialect=dialect,
5813        copy=copy,
5814        **opts,
5815    )
5816
5817
5818def and_(
5819    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5820) -> Condition:
5821    """
5822    Combine multiple conditions with an AND logical operator.
5823
5824    Example:
5825        >>> and_("x=1", and_("y=1", "z=1")).sql()
5826        'x = 1 AND (y = 1 AND z = 1)'
5827
5828    Args:
5829        *expressions: the SQL code strings to parse.
5830            If an Expression instance is passed, this is used as-is.
5831        dialect: the dialect used to parse the input expression.
5832        copy: whether or not to copy `expressions` (only applies to Expressions).
5833        **opts: other options to use to parse the input expressions.
5834
5835    Returns:
5836        And: the new condition
5837    """
5838    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5839
5840
5841def or_(
5842    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5843) -> Condition:
5844    """
5845    Combine multiple conditions with an OR logical operator.
5846
5847    Example:
5848        >>> or_("x=1", or_("y=1", "z=1")).sql()
5849        'x = 1 OR (y = 1 OR z = 1)'
5850
5851    Args:
5852        *expressions: the SQL code strings to parse.
5853            If an Expression instance is passed, this is used as-is.
5854        dialect: the dialect used to parse the input expression.
5855        copy: whether or not to copy `expressions` (only applies to Expressions).
5856        **opts: other options to use to parse the input expressions.
5857
5858    Returns:
5859        Or: the new condition
5860    """
5861    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5862
5863
5864def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5865    """
5866    Wrap a condition with a NOT operator.
5867
5868    Example:
5869        >>> not_("this_suit='black'").sql()
5870        "NOT this_suit = 'black'"
5871
5872    Args:
5873        expression: the SQL code string to parse.
5874            If an Expression instance is passed, this is used as-is.
5875        dialect: the dialect used to parse the input expression.
5876        copy: whether to copy the expression or not.
5877        **opts: other options to use to parse the input expressions.
5878
5879    Returns:
5880        The new condition.
5881    """
5882    this = condition(
5883        expression,
5884        dialect=dialect,
5885        copy=copy,
5886        **opts,
5887    )
5888    return Not(this=_wrap(this, Connector))
5889
5890
5891def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5892    """
5893    Wrap an expression in parentheses.
5894
5895    Example:
5896        >>> paren("5 + 3").sql()
5897        '(5 + 3)'
5898
5899    Args:
5900        expression: the SQL code string to parse.
5901            If an Expression instance is passed, this is used as-is.
5902        copy: whether to copy the expression or not.
5903
5904    Returns:
5905        The wrapped expression.
5906    """
5907    return Paren(this=maybe_parse(expression, copy=copy))
5908
5909
5910SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5911
5912
5913@t.overload
5914def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5915    ...
5916
5917
5918@t.overload
5919def to_identifier(
5920    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5921) -> Identifier:
5922    ...
5923
5924
5925def to_identifier(name, quoted=None, copy=True):
5926    """Builds an identifier.
5927
5928    Args:
5929        name: The name to turn into an identifier.
5930        quoted: Whether or not force quote the identifier.
5931        copy: Whether or not to copy name if it's an Identifier.
5932
5933    Returns:
5934        The identifier ast node.
5935    """
5936
5937    if name is None:
5938        return None
5939
5940    if isinstance(name, Identifier):
5941        identifier = maybe_copy(name, copy)
5942    elif isinstance(name, str):
5943        identifier = Identifier(
5944            this=name,
5945            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5946        )
5947    else:
5948        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5949    return identifier
5950
5951
5952def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
5953    """
5954    Parses a given string into an identifier.
5955
5956    Args:
5957        name: The name to parse into an identifier.
5958        dialect: The dialect to parse against.
5959
5960    Returns:
5961        The identifier ast node.
5962    """
5963    try:
5964        expression = maybe_parse(name, dialect=dialect, into=Identifier)
5965    except ParseError:
5966        expression = to_identifier(name)
5967
5968    return expression
5969
5970
5971INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5972
5973
5974def to_interval(interval: str | Literal) -> Interval:
5975    """Builds an interval expression from a string like '1 day' or '5 months'."""
5976    if isinstance(interval, Literal):
5977        if not interval.is_string:
5978            raise ValueError("Invalid interval string.")
5979
5980        interval = interval.this
5981
5982    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5983
5984    if not interval_parts:
5985        raise ValueError("Invalid interval string.")
5986
5987    return Interval(
5988        this=Literal.string(interval_parts.group(1)),
5989        unit=Var(this=interval_parts.group(2)),
5990    )
5991
5992
5993@t.overload
5994def to_table(sql_path: str | Table, **kwargs) -> Table:
5995    ...
5996
5997
5998@t.overload
5999def to_table(sql_path: None, **kwargs) -> None:
6000    ...
6001
6002
6003def to_table(
6004    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
6005) -> t.Optional[Table]:
6006    """
6007    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6008    If a table is passed in then that table is returned.
6009
6010    Args:
6011        sql_path: a `[catalog].[schema].[table]` string.
6012        dialect: the source dialect according to which the table name will be parsed.
6013        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6014
6015    Returns:
6016        A table expression.
6017    """
6018    if sql_path is None or isinstance(sql_path, Table):
6019        return sql_path
6020    if not isinstance(sql_path, str):
6021        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6022
6023    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6024    if table:
6025        for k, v in kwargs.items():
6026            table.set(k, v)
6027
6028    return table
6029
6030
6031def to_column(sql_path: str | Column, **kwargs) -> Column:
6032    """
6033    Create a column from a `[table].[column]` sql path. Schema is optional.
6034
6035    If a column is passed in then that column is returned.
6036
6037    Args:
6038        sql_path: `[table].[column]` string
6039    Returns:
6040        Table: A column expression
6041    """
6042    if sql_path is None or isinstance(sql_path, Column):
6043        return sql_path
6044    if not isinstance(sql_path, str):
6045        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6046    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6047
6048
6049def alias_(
6050    expression: ExpOrStr,
6051    alias: str | Identifier,
6052    table: bool | t.Sequence[str | Identifier] = False,
6053    quoted: t.Optional[bool] = None,
6054    dialect: DialectType = None,
6055    copy: bool = True,
6056    **opts,
6057):
6058    """Create an Alias expression.
6059
6060    Example:
6061        >>> alias_('foo', 'bar').sql()
6062        'foo AS bar'
6063
6064        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6065        '(SELECT 1, 2) AS bar(a, b)'
6066
6067    Args:
6068        expression: the SQL code strings to parse.
6069            If an Expression instance is passed, this is used as-is.
6070        alias: the alias name to use. If the name has
6071            special characters it is quoted.
6072        table: Whether or not to create a table alias, can also be a list of columns.
6073        quoted: whether or not to quote the alias
6074        dialect: the dialect used to parse the input expression.
6075        copy: Whether or not to copy the expression.
6076        **opts: other options to use to parse the input expressions.
6077
6078    Returns:
6079        Alias: the aliased expression
6080    """
6081    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6082    alias = to_identifier(alias, quoted=quoted)
6083
6084    if table:
6085        table_alias = TableAlias(this=alias)
6086        exp.set("alias", table_alias)
6087
6088        if not isinstance(table, bool):
6089            for column in table:
6090                table_alias.append("columns", to_identifier(column, quoted=quoted))
6091
6092        return exp
6093
6094    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6095    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6096    # for the complete Window expression.
6097    #
6098    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6099
6100    if "alias" in exp.arg_types and not isinstance(exp, Window):
6101        exp.set("alias", alias)
6102        return exp
6103    return Alias(this=exp, alias=alias)
6104
6105
6106def subquery(
6107    expression: ExpOrStr,
6108    alias: t.Optional[Identifier | str] = None,
6109    dialect: DialectType = None,
6110    **opts,
6111) -> Select:
6112    """
6113    Build a subquery expression.
6114
6115    Example:
6116        >>> subquery('select x from tbl', 'bar').select('x').sql()
6117        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6118
6119    Args:
6120        expression: the SQL code strings to parse.
6121            If an Expression instance is passed, this is used as-is.
6122        alias: the alias name to use.
6123        dialect: the dialect used to parse the input expression.
6124        **opts: other options to use to parse the input expressions.
6125
6126    Returns:
6127        A new Select instance with the subquery expression included.
6128    """
6129
6130    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6131    return Select().from_(expression, dialect=dialect, **opts)
6132
6133
6134def column(
6135    col: str | Identifier,
6136    table: t.Optional[str | Identifier] = None,
6137    db: t.Optional[str | Identifier] = None,
6138    catalog: t.Optional[str | Identifier] = None,
6139    quoted: t.Optional[bool] = None,
6140) -> Column:
6141    """
6142    Build a Column.
6143
6144    Args:
6145        col: Column name.
6146        table: Table name.
6147        db: Database name.
6148        catalog: Catalog name.
6149        quoted: Whether to force quotes on the column's identifiers.
6150
6151    Returns:
6152        The new Column instance.
6153    """
6154    return Column(
6155        this=to_identifier(col, quoted=quoted),
6156        table=to_identifier(table, quoted=quoted),
6157        db=to_identifier(db, quoted=quoted),
6158        catalog=to_identifier(catalog, quoted=quoted),
6159    )
6160
6161
6162def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6163    """Cast an expression to a data type.
6164
6165    Example:
6166        >>> cast('x + 1', 'int').sql()
6167        'CAST(x + 1 AS INT)'
6168
6169    Args:
6170        expression: The expression to cast.
6171        to: The datatype to cast to.
6172
6173    Returns:
6174        The new Cast instance.
6175    """
6176    expression = maybe_parse(expression, **opts)
6177    data_type = DataType.build(to, **opts)
6178    expression = Cast(this=expression, to=data_type)
6179    expression.type = data_type
6180    return expression
6181
6182
6183def table_(
6184    table: Identifier | str,
6185    db: t.Optional[Identifier | str] = None,
6186    catalog: t.Optional[Identifier | str] = None,
6187    quoted: t.Optional[bool] = None,
6188    alias: t.Optional[Identifier | str] = None,
6189) -> Table:
6190    """Build a Table.
6191
6192    Args:
6193        table: Table name.
6194        db: Database name.
6195        catalog: Catalog name.
6196        quote: Whether to force quotes on the table's identifiers.
6197        alias: Table's alias.
6198
6199    Returns:
6200        The new Table instance.
6201    """
6202    return Table(
6203        this=to_identifier(table, quoted=quoted) if table else None,
6204        db=to_identifier(db, quoted=quoted) if db else None,
6205        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6206        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6207    )
6208
6209
6210def values(
6211    values: t.Iterable[t.Tuple[t.Any, ...]],
6212    alias: t.Optional[str] = None,
6213    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6214) -> Values:
6215    """Build VALUES statement.
6216
6217    Example:
6218        >>> values([(1, '2')]).sql()
6219        "VALUES (1, '2')"
6220
6221    Args:
6222        values: values statements that will be converted to SQL
6223        alias: optional alias
6224        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6225         If either are provided then an alias is also required.
6226
6227    Returns:
6228        Values: the Values expression object
6229    """
6230    if columns and not alias:
6231        raise ValueError("Alias is required when providing columns")
6232
6233    return Values(
6234        expressions=[convert(tup) for tup in values],
6235        alias=(
6236            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6237            if columns
6238            else (TableAlias(this=to_identifier(alias)) if alias else None)
6239        ),
6240    )
6241
6242
6243def var(name: t.Optional[ExpOrStr]) -> Var:
6244    """Build a SQL variable.
6245
6246    Example:
6247        >>> repr(var('x'))
6248        '(VAR this: x)'
6249
6250        >>> repr(var(column('x', table='y')))
6251        '(VAR this: x)'
6252
6253    Args:
6254        name: The name of the var or an expression who's name will become the var.
6255
6256    Returns:
6257        The new variable node.
6258    """
6259    if not name:
6260        raise ValueError("Cannot convert empty name into var.")
6261
6262    if isinstance(name, Expression):
6263        name = name.name
6264    return Var(this=name)
6265
6266
6267def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6268    """Build ALTER TABLE... RENAME... expression
6269
6270    Args:
6271        old_name: The old name of the table
6272        new_name: The new name of the table
6273
6274    Returns:
6275        Alter table expression
6276    """
6277    old_table = to_table(old_name)
6278    new_table = to_table(new_name)
6279    return AlterTable(
6280        this=old_table,
6281        actions=[
6282            RenameTable(this=new_table),
6283        ],
6284    )
6285
6286
6287def convert(value: t.Any, copy: bool = False) -> Expression:
6288    """Convert a python value into an expression object.
6289
6290    Raises an error if a conversion is not possible.
6291
6292    Args:
6293        value: A python object.
6294        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6295
6296    Returns:
6297        Expression: the equivalent expression object.
6298    """
6299    if isinstance(value, Expression):
6300        return maybe_copy(value, copy)
6301    if isinstance(value, str):
6302        return Literal.string(value)
6303    if isinstance(value, bool):
6304        return Boolean(this=value)
6305    if value is None or (isinstance(value, float) and math.isnan(value)):
6306        return NULL
6307    if isinstance(value, numbers.Number):
6308        return Literal.number(value)
6309    if isinstance(value, datetime.datetime):
6310        datetime_literal = Literal.string(
6311            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6312        )
6313        return TimeStrToTime(this=datetime_literal)
6314    if isinstance(value, datetime.date):
6315        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6316        return DateStrToDate(this=date_literal)
6317    if isinstance(value, tuple):
6318        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6319    if isinstance(value, list):
6320        return Array(expressions=[convert(v, copy=copy) for v in value])
6321    if isinstance(value, dict):
6322        return Map(
6323            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6324            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6325        )
6326    raise ValueError(f"Cannot convert {value}")
6327
6328
6329def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6330    """
6331    Replace children of an expression with the result of a lambda fun(child) -> exp.
6332    """
6333    for k, v in expression.args.items():
6334        is_list_arg = type(v) is list
6335
6336        child_nodes = v if is_list_arg else [v]
6337        new_child_nodes = []
6338
6339        for cn in child_nodes:
6340            if isinstance(cn, Expression):
6341                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6342                    new_child_nodes.append(child_node)
6343                    child_node.parent = expression
6344                    child_node.arg_key = k
6345            else:
6346                new_child_nodes.append(cn)
6347
6348        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6349
6350
6351def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6352    """
6353    Return all table names referenced through columns in an expression.
6354
6355    Example:
6356        >>> import sqlglot
6357        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6358        ['a', 'c']
6359
6360    Args:
6361        expression: expression to find table names.
6362        exclude: a table name to exclude
6363
6364    Returns:
6365        A list of unique names.
6366    """
6367    return {
6368        table
6369        for table in (column.table for column in expression.find_all(Column))
6370        if table and table != exclude
6371    }
6372
6373
6374def table_name(table: Table | str, dialect: DialectType = None) -> str:
6375    """Get the full name of a table as a string.
6376
6377    Args:
6378        table: Table expression node or string.
6379        dialect: The dialect to generate the table name for.
6380
6381    Examples:
6382        >>> from sqlglot import exp, parse_one
6383        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6384        'a.b.c'
6385
6386    Returns:
6387        The table name.
6388    """
6389
6390    table = maybe_parse(table, into=Table, dialect=dialect)
6391
6392    if not table:
6393        raise ValueError(f"Cannot parse {table}")
6394
6395    return ".".join(
6396        part.sql(dialect=dialect, identify=True)
6397        if not SAFE_IDENTIFIER_RE.match(part.name)
6398        else part.name
6399        for part in table.parts
6400    )
6401
6402
6403def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6404    """Replace all tables in expression according to the mapping.
6405
6406    Args:
6407        expression: expression node to be transformed and replaced.
6408        mapping: mapping of table names.
6409        copy: whether or not to copy the expression.
6410
6411    Examples:
6412        >>> from sqlglot import exp, parse_one
6413        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6414        'SELECT * FROM c'
6415
6416    Returns:
6417        The mapped expression.
6418    """
6419
6420    def _replace_tables(node: Expression) -> Expression:
6421        if isinstance(node, Table):
6422            new_name = mapping.get(table_name(node))
6423            if new_name:
6424                return to_table(
6425                    new_name,
6426                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6427                )
6428        return node
6429
6430    return expression.transform(_replace_tables, copy=copy)
6431
6432
6433def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6434    """Replace placeholders in an expression.
6435
6436    Args:
6437        expression: expression node to be transformed and replaced.
6438        args: positional names that will substitute unnamed placeholders in the given order.
6439        kwargs: keyword arguments that will substitute named placeholders.
6440
6441    Examples:
6442        >>> from sqlglot import exp, parse_one
6443        >>> replace_placeholders(
6444        ...     parse_one("select * from :tbl where ? = ?"),
6445        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6446        ... ).sql()
6447        "SELECT * FROM foo WHERE str_col = 'b'"
6448
6449    Returns:
6450        The mapped expression.
6451    """
6452
6453    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6454        if isinstance(node, Placeholder):
6455            if node.name:
6456                new_name = kwargs.get(node.name)
6457                if new_name:
6458                    return convert(new_name)
6459            else:
6460                try:
6461                    return convert(next(args))
6462                except StopIteration:
6463                    pass
6464        return node
6465
6466    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6467
6468
6469def expand(
6470    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6471) -> Expression:
6472    """Transforms an expression by expanding all referenced sources into subqueries.
6473
6474    Examples:
6475        >>> from sqlglot import parse_one
6476        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6477        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6478
6479        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6480        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6481
6482    Args:
6483        expression: The expression to expand.
6484        sources: A dictionary of name to Subqueryables.
6485        copy: Whether or not to copy the expression during transformation. Defaults to True.
6486
6487    Returns:
6488        The transformed expression.
6489    """
6490
6491    def _expand(node: Expression):
6492        if isinstance(node, Table):
6493            name = table_name(node)
6494            source = sources.get(name)
6495            if source:
6496                subquery = source.subquery(node.alias or name)
6497                subquery.comments = [f"source: {name}"]
6498                return subquery.transform(_expand, copy=False)
6499        return node
6500
6501    return expression.transform(_expand, copy=copy)
6502
6503
6504def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6505    """
6506    Returns a Func expression.
6507
6508    Examples:
6509        >>> func("abs", 5).sql()
6510        'ABS(5)'
6511
6512        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6513        'CAST(5 AS DOUBLE)'
6514
6515    Args:
6516        name: the name of the function to build.
6517        args: the args used to instantiate the function of interest.
6518        dialect: the source dialect.
6519        kwargs: the kwargs used to instantiate the function of interest.
6520
6521    Note:
6522        The arguments `args` and `kwargs` are mutually exclusive.
6523
6524    Returns:
6525        An instance of the function of interest, or an anonymous function, if `name` doesn't
6526        correspond to an existing `sqlglot.expressions.Func` class.
6527    """
6528    if args and kwargs:
6529        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6530
6531    from sqlglot.dialects.dialect import Dialect
6532
6533    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6534    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6535
6536    parser = Dialect.get_or_raise(dialect)().parser()
6537    from_args_list = parser.FUNCTIONS.get(name.upper())
6538
6539    if from_args_list:
6540        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6541    else:
6542        kwargs = kwargs or {"expressions": converted}
6543        function = Anonymous(this=name, **kwargs)
6544
6545    for error_message in function.error_messages(converted):
6546        raise ValueError(error_message)
6547
6548    return function
6549
6550
6551def case(
6552    expression: t.Optional[ExpOrStr] = None,
6553    **opts,
6554) -> Case:
6555    """
6556    Initialize a CASE statement.
6557
6558    Example:
6559        case().when("a = 1", "foo").else_("bar")
6560
6561    Args:
6562        expression: Optionally, the input expression (not all dialects support this)
6563        **opts: Extra keyword arguments for parsing `expression`
6564    """
6565    if expression is not None:
6566        this = maybe_parse(expression, **opts)
6567    else:
6568        this = None
6569    return Case(this=this, ifs=[])
6570
6571
6572def true() -> Boolean:
6573    """
6574    Returns a true Boolean expression.
6575    """
6576    return Boolean(this=True)
6577
6578
6579def false() -> Boolean:
6580    """
6581    Returns a false Boolean expression.
6582    """
6583    return Boolean(this=False)
6584
6585
6586def null() -> Null:
6587    """
6588    Returns a Null expression.
6589    """
6590    return Null()
6591
6592
6593# TODO: deprecate this
6594TRUE = Boolean(this=True)
6595FALSE = Boolean(this=False)
6596NULL = Null()